SQL Server 2008- Pass Table as Parameter to Stored Procedure Example

Introduction:

In this article I will explain how to pass table as parameter to stored procedure in SQL Server 2008.

Description:

In SQL Server 2008 Microsoft has introduced new feature called Table value parameters by using this concept we can directly pass table as parameter in stored procedures or functions without creating temporary table or many parameters.  

Before enter into this concept first create one table in your database and give name as EmployeeDetailsbecause we are going to use this table to insert data

/* Create a new table */ CREATE TABLE EmployeeDetails( EmpId INT NOT NULL, EmpName VARCHAR(50) NULL, [Role] VARCHAR(50) NULL ) ON [PRIMARY]

To use Table value parameters we need to follow below steps

        1) First we need to create Table type

Create Table Type

/* Create a table type. */ CREATE TYPE EmpDetailsType AS TABLE ( EmployeeId INT, EmployeeName VARCHAR(50), EmpRole VARCHAR(50) )

          2) Now we need to create stored procedure which contains Table Type as Parameter

CREATE PROCEDURE prc_InsertEmpDetails ( @TVP EmpDetailsType READONLY ) As INSERT INTO EmployeeDetails(EmpId,EmpName,[Role]) SELECT * FROM @TVP; GO

If you observe above procedure I am using table type parameter to insert data into EmployeeDetailstable. Here we need to remember some of important Points those are

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

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

We completed creation of table type and procedure with table type as parameter now we can test this table value parameter concept by passing table type as parameter to the procedure.

For that declare table type variable and reference the previously created table type and insert some data into table type parameter like as shown below. After that pass table type value to procedure  

DECLARE @EmpDetailsTVP AS EmpDetailsType INSERT INTO @EmpDetailsTVP(EmployeeId,EmployeeName,EmpRole) VALUES(2,'Mahesh','Developer'), (3,'Prasanthi','Consultant'), (4,'Madhav','Analyst'), (5,'Nagaraju','Developer') EXEC prc_InsertEmpDetails @EmpDetailsTVP

Once run the above query now check the your EmployeeDetails table with below Query

SELECT * FROM EmployeeDetails

Output

This way we can pass table as parameter in procedures or functions.