Introduction to Table-Valued Parameters with Example

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

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

Create a table type and define the table structure
Declare a stored procedure that has a parameter of table type.
Declare a table type variable and reference the table type.
Using the INSERT statement and occupy the variable.
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

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,’A’),(2,’B’),(3,’C’),(4,’D’),(5,’E’);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s