Table-Valued Parameters in SQL Server

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.

1. 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.

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

Structure data type accepts DataTable, DbDataReader or IEnumarable. In the following example, the first is using a data table, while the second is using IEnumarable 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();`

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.