How to pass table as parameter in stored procedures in SQL Server (Table-Valued Parameters)

In this post I will explain how to pass table as parameter in stored procedure with example.

Table-Valued Parameters is a new feature of Microsoft SQL Server 2008. In earlier versions of SQL Server it is not possible to pass table variable in store procedure as parameter. You 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.

Let’s go into practical implementation. Let’s create a table named EmployeeInfo and pass the table variable to insert data using stored procedure.

/*Create New table*/

Create Table EmployeeInfo

(

EmpId int not null,

EmpName varchar(50) not null,

EmpAge int not null,

EmpContact varchar(20) null

) on [Primary]

Now to use the Table-Valued Parameters we need follow the steps as shown below: 

1) First we need to create the user-defined table type.

/*Create user Defined table Type*/

Create Type EmployeeInfoType as Table

(

EmpId int,

EmpName varchar(50),

EmpAge int,

EmpContact varchar(20)

)

2) Now create a stored procedure which accepts Table Type as parameter.

/*Create Store Procedure*/

Create Procedure InsertEmployeeInfo

(

@EmpInfo as EmployeeInfoType ReadOnly

)

AS

Insert into dbo.EmployeeInfo(EmpID, Empname, EmpAge,EmpContact)

select * from @EmpInfo

In the above procedure, you can see that I have used Table Type parameter (@EmpInfo) to insert data into   EmployeeInfo table.

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) Now declare a table type variable.

Declare @EmpInfoTVP as EmployeeInfoType

4) Using the INSERT statement and occupy the variable.

Insert Into @EmpInfoTVP values 
('1','Manish','25','9293561754'), 
('2','Shivendra','26','6856856347'), 
('3','Rahul','22','7459324576'),

('4','Prashant','24','1234518750')

5) Now you can pass the variable to procedure and execute

Exec InsertEmployeeInfo @EmpInfoTVP

After Execution of procedure now you can check your EmployeeInfo table with below query.

SELECT * FROM EmployeeInfo

Below is the Output