Saturday, January 19, 2013

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
kick it on DotNetKicks.com

1 comment:

^ Scroll to Top