Pass Tables to Stored Procedure / Table Valued Parameter
page 2 of 3
by Aamod Thakur
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 119641/ 44

Table-Valued Parameters

SQL Server 2008 Provides a New Feature Called Table-Valued Parameters

This provides us to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

We first need to create a user defined type

Database Node > Programmability > Types > User-Defined Table Types

Script to create a User-Defined Table type

--Create User-defined Table Type
 CREATE TYPE dbo.MessageQueue AS TABLE 
    id int PRIMARY KEY, 
    MessageType varchar(20) NOT NULL,  
    MessageContent varchar(1000) NOT NULL,
    PushDate datetime NOT NULL DEFAULT GETDATE()
 --Using the User-Defined Table Type
 DECLARE @MyMessageQueue MessageQueue
 INSERT INTO @MyMessageQueue(id ,MessageType,MessageContent,PushDate)
 VALUES (1,'SMS','Hello World','2009-09-30 10:00:00'),
          (2,'SMS','Hello World','2009-09-30 10:00:00'),
          (3,'MMS','Happy Diwali','2009-10-17 10:00:00')
 -- Select the inserted records using new type
 SELECT * FROM @MyMessageQueue 

Using the Variable as a Parameter

The benefit of User-Defined Table Type is that it can be passed to a stored procedure. Below is an example of using the newly created type with stored procedure

Using the user defined table type in Stored Procedure

CREATE TABLE [dbo].[MyMessageTable] 
 id [intPRIMARY KEY,
 MessageType varchar(20) NULL,
 MessageContent varchar(1000) NOT NULL,
 PushDate datetime NULL,
 EngineID int  NOT NULL
 CREATE PROCEDURE usp_InsertMessages 
 @MyParameter MessageQueue READONLY,
 @EngineId varchar(20)
 INSERT INTO MyMessageTable(id,MessageType,MessageContent,PushDate,EngineID)
 SELECT id,MessageType,MessageContent,PushDate,@EngineId
 FROM @MyParameter 
 --<where condition if any> for the table valued parameter
 --Using the User-Defined Table Type in stored procedure
 DECLARE @MyMessageQueue MessageQueue
 INSERT INTO @MyMessageQueue(id ,MessageType,MessageContent,PushDate)
 VALUES (1,'SMS','Hello World','2009-09-30 10:00:00'),
          (2,'SMS','Hello World','2009-09-30 10:00:00'),
          (3,'MMS','Happy Diwali','2009-10-17 10:00:00')
 EXEC usp_InsertMessages @MyMessageQueue,007
 -- Select the records inserted using Stored procedure
 SELECT * FROM MyMessageTable 


In order to use the user defined type user must have execute permission on the type. if user doesn't have execute permission on the same,it can be granted using below statement:

GRANT EXECUTE ON TYPE::dbo.MessageQueue TO <User Name>;

I will be providing the C# code for this article for using this feature in .NET applications in next article or you can refer below mentioned MSDN article link for more details


View Entire Article

User Comments

Title: nfl jerseys cheap   
Name: NIKE NFL jerseys
Date: 2012-07-02 10:09:54 AM
We are professional jerseys manufacturer from china, nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes
,heap jerseys,2012 nike nfl Jerseys,nba jersey and shorts,oklahoma city thunder jersey,official jeremy lin new york knicks jersey,NFL Jerseys Wholesale,blake griffin jersey blue,NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
,Wholesale cheap jerseys,Cheap mlb jerseys,]Nike NFL Jerseys,Cheap China Wholesae,Wholesale jerseys From China,2012 nike nfl Jerseys,Jerseys From China,,2012 nike nfl Jerseys,Revolution 30 nba jerseys,jersey of nba chicago bulls direk rose ,nfl jerseys,green bay packers jerseys wholesale,Buffalo Bills nike nfl jerseys sale,good supplier soccer jerseys,cool base mlb jerseys,Revolution 30 nba jerseys,2012 stanley cup nhl jersey,
We are professional jerseys manufacturer from china, nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes.
Title: ERROR   
Date: 2012-03-17 2:06:27 AM
Title: Loosing time   
Name: Radu
Date: 2011-11-07 9:40:59 AM
This article was a totaly waste of my time.
Title: Disappointed   
Name: Ajai
Date: 2010-07-12 12:22:46 AM
You never mentioned how to execute this stored proc from c# or Sql window itself.
Title: Pass TVP in another sproc   
Name: ak
Date: 2010-04-26 2:40:45 PM
How would i pass TVP to sproc#2 from inside sproc #1
exec @MyParameter MessageQueue READONLY
is this correct?
Title: Disaapointed   
Name: Ajay
Date: 2010-04-13 11:05:33 AM
Good article but not what i am looking for
Title: Good article   
Name: Chitta Ranjan Nayak
Date: 2010-02-26 9:40:36 AM
Really a good article.
Title: good article   
Name: manas sahu
Date: 2009-12-23 8:46:08 AM
Good article but not what i m looking for.
Title: Title misleading   
Name: Chin Kiat
Date: 2009-12-08 1:26:10 AM
The title is not match to what you are posting...i understand why jeewan disaapointed =(
Title: disappointed   
Name: jeewan
Date: 2009-11-20 5:48:05 AM
Not what i am searching

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-22 10:24:39 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search