Table Valued Parameter – SQL 2008

A new T-SQL feature is available in SQL Server 2008 which allows us to pass a set of rows as a parameter to stored procedures or functions. Table-Valued Parameter (TVP) allows us to send multiple rows of data to a stored procedure or a function without the need to pass so many parameters or creating temp tables.

I have the following table for which I am going to formulate the steps of using TVP:

--Creating a table which holds employee information
CREATE TABLE Employee
(
EmployeeId INT,
Name VARCHAR(50),
PhoneNo INT
) 

Step1:

Creating a Table type and define its structure as shown below,

--Creating a table type
CREATE TYPE EmpType AS TABLE
(
EmployeeId INT,
Name VARCHAR(50),
PhoneNo INT
)

The below query output indicates whether the Type is an user defined type and if its a table type.

SELECT * FROM sys.types WHERE is_user_defined=1 AND is_table_type=1

Use the below command to grant access to the type we had created above,

GRANT EXECUTE ON TYPE::dbo.Emp TO dbuser 

We can view the above created type in SSMS > Object explorer > Expand dbname > Types.

Step2:

Creating a procedure which has a parameter of the above mentioned Table type. We need to use only a READONLY parameter to the stored procedure.

--Creating procedure to insert data
CREATE PROCEDURE InsertEmployee
@Emp EmpType READONLY
AS
INSERT INTO Employee
SELECT * FROM @Emp

The below query indicates will return all the objects which have a read only parameter as the above procedure.

SELECT OBJECT_NAME(OBJECT_ID) AS Objectname, name AS Parametername FROM sys.parameters
WHERE is_readonly=1

Step3:

We need to declare a table variable, populate the data into the table variable and then Execute the procedure.

--Populate data into table variable and calling the sp
DECLARE @Tablevariable AS EmpType
INSERT INTO @Tablevariable
VALUES(1000, 'ABC',918789890),
(1001, 'DEF',918782190),
(1002, 'GHI',918789430),
(1003, 'JKL',918787990),
(1004, 'MNO',918489890),
(1005, 'PQR',918869890)
EXEC deepak.dbo.InsertEmployee @Emp=@Tablevariable

Step4:

Lets check if the data is available in the Employee table using the below query. It will return 5 records.

SELECT * FROM Employee

Advantages:

1. TVP do not acquire locks for the initial population of data from client
2. They perform well for inserting <1000 rows

Disadvantages:

1. Statistics are not maintained on columns in TVP
2. TVP parameter can only be Read only and hence DML operations like INSERT, UPDATE and DELETE are not permitted.
3. TVP can be used in the FROM clause of SELECT INTO or in INSERT EXEC string or stored procedure. However they cannot be used as a target of SELECT INTO or INSERT EXEC statements.