Table Valued Parameters in SQL Server

Hi All,

Today will discuss table valued parameters in SQL Server.

Table Valued Parameter is a new feature introduced in SQL SERVER 2008. Table Valued Parameter allows a table to be passed as a parameter to a stored procedure from T-SQL code or from an application. In before versions of SQL Server 2008 passing multiple rows of data to stored procedure or function was not handled.

Let see how to pass multiple rows to a stored procedure using Table Valued Parameter with an example.

SQL Script to create the Employees table
Create Table Employees
(
Id INT primary key,
Name nvarchar(50),
Gender nvarchar(10)
)
Go
Step 1 : Create User-defined Table Type

CREATE TYPE ETblType AS TABLE
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
Go

Step 2 : Use the User-defined Table Type as a parameter in the stored procedure.

CREATE PROCEDURE spInsertEmployees
@EmpTableType EmpTableType READONLY
AS
BEGIN
INSERT INTO Employees
SELECT * FROM @EmpTableType
END

Step 3 : Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure.

DECLARE @EmployeeTableType EmpTableType

INSERT INTO @EmployeeTableType VALUES (1, 'Mark', 'Male')
INSERT INTO @EmployeeTableType VALUES (2, 'Mary', 'Female')
INSERT INTO @EmployeeTableType VALUES (3, 'John', 'Male')
INSERT INTO @EmployeeTableType VALUES (4, 'Sara', 'Female')
INSERT INTO @EmployeeTableType VALUES (5, 'Rob', 'Male')

EXECUTE spInsertEmployees @EmployeeTableType

Select the data from Employees table and notice that all the rows of the table variable are inserted into the Employees table.

NOTE: Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.