How to pass table as a parameter to SP?

In earlier versions of SQL SERVER before SQL SERVER 2008, it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we 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.

Steps to pass table value parameter to stored procedure

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

Let’s take an example to see how pass table variable as a parameter to stored procedure

First create a table type and define columns in it

create type myTableType as table

(id int not null primary key,

name varchar(12))

Command(s) completed successfully.

Second, Create a stored procedure which will be taking a parameter of type Table ie myTableType

create procedure myProc

@tableVariable myTableType readonly

as

begin

      select * from @tableVariable

end  

Command(s) completed successfully.

Now lets declare a variable of type Table ie myTableType and insert few records in it and execute our procedure by passing the table varible

declare @tableParameter myTableType

insert into @tableParameter values(1,'Roshna')

insert into @tableParameter values(2,'Sanjana')

      

--lets execute the sp by passing table variable as a parameter

exec myProc @tableParameter 

(1 row(s) affected)

id          name

----------- ------------

1           Roshna

2           Sanjana

(2 row(s) affected)

With table value parameter we can pass complete datable from front end application to the stored procedure, instead of one by one row. Hopefully this solution in SQL Server 2008 will get rid of all the now unnecessary “hacks” that used XML, comma separated lists.

Code it and unwind self  

Post Reference: Vikram Aristocratic Elfin Share