SQL - Table-Valued Parameters (TVPs)

Table-Valued Parameter is the most amazing feature in SQL Server which was introduced in SQL Server 2012. A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. We can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions. 

This is extremely useful in and of itself, but arguably the most compelling feature of TVPs is their ability to marshal an entire set of rows across the network, from your .NET client to your SQL Server database, with a single stored procedure call (one roundtrip) and a single table-valued parameter.

A TVP is based on a user-defined table type, which is capable to describe the schema for a set of rows that can be passed to stored procedures and UDFs. It’s helpful to begin understanding TVPs by first comparing them to similar “set” constructs, such as table variables, temp tables, and Common Table Expressions (CTEs). User can treat a TVP, table variable, temporary table, or CTE just like an ordinary table or view in virtually any scenario.

As we know that CTEs and table variables store their row data in memory—assuming reasonably sized sets that don’t overflow the RAM cache allocated for them, in which case, they do push their data into tempdb. In contrast, a TVP’s data is always stored in tempdb. When user first populates a TVP, SQL Server creates a table in tempdb to back that TVP as it gets passed from one stored procedure (or UDF) to another. Once the stack unwinds and the TVP falls out of scope in your T-SQL code, SQL Server cleans up tempdb automatically. User never interacts directly with tempdb, because TVPs provide a total abstraction over it.

How to create Table Value Type Parameter in database

Table-valued parameters are declared by using user-defined table types. 

----- Create Type in database with body Create Type Employeestvp as Table ( EmpId Varchar(8), EmployeeName Varchar(30), DeptId Varchar(5), EmployeeRatePerDay decimal(18,2) )

TVP types are displayed in Visual Studio’s SQL Server Object Explorer in the User-Defined Table Types node beneath Programmability | Types as shown below:

User-defined table types that can be used for TVPs displayed in SQL Server Object Explorer

How to use TVPs to insert values-

The procedure doesn’t know or care how the caller populates the TVP before it is used as the source for the INSERT INTO…SELECT statement. For example, the caller could manually add one row at a time, as follows:   

---- declare variable TVPs type DECLARE @Employeestvp AS Employeestvp ------ insert values in tvps variable   INSERT INTO @Employeestvp VALUES ('E0010', 'Arjun Smith', 'D001', 191 ) INSERT INTO @Employeestvp VALUES ('E0011', 'Lucy White', 'D001', 185) INSERT INTO @Employeestvp VALUES ('E0012', 'Lucky Gray', 'D001', 178) INSERT INTO @Employeestvp VALUES ('E0013', 'Rose Smith', 'D002', 172) INSERT INTO @Employeestvp VALUES ('E0014', 'Read Milk', 'D002', 166) ---- select values from the variables here  SELECT * FROM @EmployeesTVP Emp Id Employee Name Dept Id EmployeeRatePerDay E0010 Arjun Smith D001 191 E0011 Lucy White D001 185 E0012 Lucky Gray D001 178 E0013 Rose Smith D002 172 E0014 Read Milk D002 166

Creating a stored procedure that accepts TVPs

Enter TVPs. Now we can create a single stored procedure having a TVPs as the parameter for the Employee Master as shown below:

---- Create Proc to insert values from table type CREATE PROCEDURE tvp_sp_InsertEmployees ( ----- define table value type parameter @EmployeesTVP as Employeestvp READONLY ) AS begin ---- Insert value into target table ---- from table value type parameter INSERT INTO [dbo].[EmployeeMaster] (EmpId,EmployeeName,DeptId, EmployeeRatePerDay) ---- Pull values from table value type parameter SELECT EmpId, EmployeeName, DeptId, EmployeeRatePerDay FROM @EmployeesTVP End

How to call stored procedure to insert values from TVPs:

Now, we have created stored procedure and defined the input parameter as table value type which we have already created in our database also. We have a program (stored procedure, table value parameter and target table), so it will be very easy to insert multiple records into our data table as given below:

---- declare table value parameter variable DECLARE @Employeestvp AS Employeestvp ------ insert values in tvps variable  INSERT INTO @Employeestvp VALUES('E0010', 'Arjun Smith', 'D001', 191 ) INSERT INTO @Employeestvp VALUES('E0011', 'Lucy White', 'D001', 185) INSERT INTO @Employeestvp VALUES('E0012', 'Lucky Gray', 'D001', 178) INSERT INTO @Employeestvp VALUES('E0013', 'Rose Smith', 'D002', 172) INSERT INTO @Employeestvp VALUES('E0014', 'Read Milk', 'D002', 166) ---- call the store procedure and pass tvp parameters      EXEC tvp_sp_InsertEmployees @Employeestvp ---- Pull Value from our data table after insert records SELECT EmpId, EmployeeName, DeptId, EmployeeRatePerDay FROM [dbo].[EmployeeMaster] Emp Id Employee Name Dept Id EmployeeRatePerDay E0010 Arjun Smith D001 191 E0011 Lucy White D001 185 E0012 Lucky Gray D001 178 E0013 Rose Smith D002 172 E0014 Read Milk D002 166

Conclusion

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. Table-valued parameters are declared by using user-defined table types.