Table Valued Parameters

They are Strongly Typed
We can specify the sort order and the unique key
Reduce Round trips to the server
We can have the table structure of different cardinality
It doesn't need or cause the statement to recompile
It doesn't need lock to initially populate the data from the client
Enables us to include complex business logic in a single routine
It provides a very simple programming model.

Table-Valued Parameters  is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1. Create a table type and define the table structure

2. Declare a stored procedure that has a parameter of table type.

3. Declare a table type variable and reference the table type.

4. Using the INSERT statement and occupy the variable.

5. We can now pass the variable to the procedure.

For Example,

Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.

Department:

CREATE TABLE Department
(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(30)
)
GO

1. Create a TABLE TYPE and define the table structure:

CREATE TYPE DeptType AS TABLE
(
DeptId INT, DeptName VARCHAR(30)
);
GO

2. Declare a STORED PROCEDURE that has a parameter of table type:

CREATE PROCEDURE InsertDepartment
@InsertDept_TVP DeptType READONLY
AS
INSERT INTO Department(DepartmentID,DepartmentName)
SELECT * FROM @InsertDept_TVP;

GO

Important points  to remember :

-  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

-  You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

3. Declare a table type variable and reference the table type.

DECLARE @DepartmentTVP AS DeptType;

4. Using the INSERT statement and occupy the variable.

INSERT INTO @DepartmentTVP(DeptId,DeptName)
VALUES (1,'Accounts'),
(2,'Purchase'),
(3,'Software'),
(4,'Stores'),
(5,'Maarketing');

5. We can now pass the variable to the procedure and Execute.

EXEC InsertDepartment @DepartmentTVP;
GO

Let’s see if the Data are inserted in the Department Table

Conclusion:

Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.