SQL Server – Table-Valued Parameters

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to create Table-Valued Parameters

1. Create a table type and define the table structure.
2. Declare a stored procedure or function that has a parameter of the table type.
3. Declare a variable of the table type, and reference the table type.
4. Fill the table variable by using an INSERT statement.
5. Now you can pass the variable to a procedure or function.

Now let ‘s try to understand with example

Create a table as below

USE testdb
Go

CREATE TABLE Users
(
UserID INT,
UsertName VARCHAR(255)
)
GO

1    Create a table type and define the table structure

CREATE TYPE UserType AS TABLE
(
UserId INT,
UserName VARCHAR(255)
)
GO

2    Declare a stored procedure or function that has a parameter of the table type

CREATE PROCEDURE InsUser
@InsUser UserType READONLY
AS
INSERT INTO Users(UserID,UsertName)
SELECT * FROM @InsUser

GO

3    Declare a variable of the table type, and reference the table type

DECLARE @User AS UserType

4    Fill the table variable by using an INSERT statement

INSERT INTO @User(UserId,UserName)
VALUES (1,'Varinder'),
(2,'Dinesh'),
(3,'Vikram'),
(4,'Ranjoyt'),
(5,'Ram');

5    Now you can pass the variable to a procedure or function

EXEC InsUser @User;
GO

Result :

See the values in tables

Note:

We created @InsUser parameter read only because Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

For more detail : http://msdn.microsoft.com/en-us/library/bb510489.aspx