Table-Valued Parameters in SQL Server

Table-Valued Parameters

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

1. Create a User-defined table type in SQL (UDTT)

You can predefine the user definition of tables with functions that are created using a schema. We also call it a schema definition that can be created using temporary data also known as UDTTs(User-defined table types).

These user-defined table types support almost the same features as normal data tables like primary keys, default values, unique constraints, etc. It is referred from a table that can be passed through parameters like stored procedures and functions.

For a table-valued parameter, we need a user-defined table type (UDTT). UDTT can be created with the following T-SQL statement.

2. Table-Valued Parameter in Stored Procedure

The utilization of table-valued parameters is almost equivalent to other parameters. For other data types, we will have to give UDTT a name. Table-Valued Parameter allows us to pass multiple columns and rows as input to the stored method.

The table values parameters must be passed through READONLY parameters. All the DML operations such as INSERT, DELETE and UPDATE cannot be stored in any procedure. You can only use functions like SELECT statements.

Given below is an example of a table-valued parameter in the stored procedure. Table Valued Parameter can’t be used as OUTPUT parameter in stored procedures.

3. Execution from C# code

To execute stored procedures from .net code, we have to define parameters as Structured parameters.

Get Shreyans Padmani’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Structure data type accepts DataTable, DbDataReader or IEnumarable<SqlDataRecord>. In the following example, the first is using a data table, while the second is using IEnumarable<SqlDataRecord> for List Records. The third example shows how to use table-valued parameters with a dapper.

Using Data Table

status void TableParameterUsingDataTable()
{  DataTable dtCurrency = new DataTable();
    dtCurrency.Columns.Add("Country", typeof(string));
    dtCurrency.Columns.Add("Currencyname", typeof(string));
    dtCurrency.Rows.Add("India", "Indian Rupee");
    dtCurrency.Rows.Add("USA", "US Dollar");
 
SqlConnection connection =new SQlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand("USP_AddCountries", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//Pass tabel valued parameter to Store Procedure
SqlParameter sqlparam = cmd.Parameters.AddWithValue("@Countries", dtCurrency);
SqlParam.SqlDbtype = SqlDbType.Structured;
cmd.ExecuteNonQuery();
connection.Close();

Using List

static void TableParameterUsingList()
{
    //Local Funtion
    IEnumerable<SqlDataRecord> CreateSQLDataRecords(IEnumerable<Country>) countries)
   {
        SqlMetaData[] metaData = new SqlmetaData[2];
        metaData[0] = new SqlMetaData("Countryname", SqlDbType.NVarChar, 100);
       metaData[1] = new SqlMetaData("Currencyname", SqlDbtype.NVarchar,50);
       SqlDataRecord record = new SqlDataRecord(metaData);
       foreach (var c in countries)
       {
           record.SetSqlString(0, c.Countryname); 
           record.SetSqlString(1, c.Currencyname);
       };
  }
  List<Country> currecnylist = new List<Country>
  {
          new Country("India", "Indian Rupee"),
          new Country("USA", "US Dollar"),
  };
IEnumerable<SqlDataRecord> sqlDataRecords = CreateSqlDataRecords(currencyList);
 
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand("USP_AddCountries", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//Pass table Valued parameter to Store Procedure
Sqlparameter sqlParam = cmd.parameters.AddWithvalue("@Countries", sqlDataRecords);
sqlParam.SqlDbtype = SqlDbtype.Structured;
cmd.ExecuteNonQuery();
connection.Close();
}

Using Dapper

static void TableparameterUsingDapper()
{ 
     List<Country> currencyList = new list<Country>
       {
            new Country("India", "Indian Rupee"),
            new Country("USA", "US Dollar")
};
DataTable dtCurrency = new DataTable();
using (var reader = ObjectReader.Create(currencyList))
{
      dtCurrecny.Load(reader);
}
//For DataTable, start from here
SqlConnection connection =  new SqlConnection(connectionString);
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@Countries", dtCurrency.AsTableValuedParameter("UDTT_Country"));
connection.Query("USP_AddCountries", parameters, commandType: CommandType.StoredProcedure);
}

Benefits of Table-Valued Parameters

• Performance Improvement: Reduces the number of database calls by sending bulk data in a single call.
• Simplified Code: Eliminates complex looping logic required for inserting multiple rows.
• Strong Typing: Ensures data consistency by defining a structure upfront.
• Efficient Memory Usage: Uses optimized data structures within SQL Server.

Drawbacks of Table-Valued Parameters

• Read-Only Limitation: TVPs cannot be modified within a stored procedure.
• Larger Memory Footprint: When handling very large datasets, TVPs are stored in memory, potentially affecting performance.
• Cannot Be Used as Output Parameter: Unlike scalar values, TVPs cannot be returned as an output parameter in stored procedures.

Conclusion

Table-Valued Parameters in SQL Server provide a powerful way to handle bulk data efficiently. By using UDTTs, we can pass structured data to stored procedures and functions, reducing the number of database calls and improving performance. However, they come with certain limitations, such as being read-only and consuming memory for large datasets. Choosing the right approach depends on the application’s requirements and expected data volume.

By implementing TVPs in your .NET applications, you can optimize performance while keeping the code clean and maintainable.