Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Sep 23, 2012
Introduction:

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

Description:

In previous articles I explained Convert Rows to Columns, Query to get data between two dates, Query to get duplicate records count, Query to remove duplicate records and many articles relating to SQL Server. Now I will explain how to pass table as parameter to stored procedure in SQL Server 2008.

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 EmployeeDetails because 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 EmployeeDetails table. 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.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

9 comments :

kshma tiwari said...

hii suresh,nice article,bt as a beginner i wanna know that why we use temp table,wht are its advantage and what is the use of whole procedure u did above..pl explain,i am waiting/
regards

pragadesh said...

Hi Mr.Suresh

In what situation we use the local temporary tables,global temporary tables in sql server please clear it from your valuable explanation

Regards
Pragadesh

Anonymous said...

When ever i create table type error occur Incorrect syntax near the keyword 'AS'.

Anonymous said...

Respected Sir , This is AbhinavSingh993 your biggest student and the fan , Your website has become wikipedia of .NET for me,
Thanks and Regards,
Abhinavsingh993
Lucknow

AbhiTheFlame said...

Hi Suresh nice article..
Can we create Types Programmatically in C# code
and drop it after Insert completed... Is this idea will worked or not ...

Anonymous said...

nice article

Anonymous said...

Hi suresh,
Nice article, it helped me to solve performance issues i was experiencing.

Anonymous said...

nice

Unknown said...

Hi Suresh, Do we need to write Insert statement for TYpe also ah? Then why for Tabled valued parameter?? we can directly insert into Our table know.

Please if possible clear this doubt.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.