<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Pass Tables to Stored Procedure / Table Valued Parameter

Published on
12,219 Points
6,119 Views
1 Endorsement
Last Modified:
Approved
Community Pick
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a SQLBulkCopy object available in .NET to send multiple rows of data to SQL Server at once, but this still cannot be passed to a stored procedure. SQL Server 2008 provides a new feature called "Table-Valued Parameters". This provides us the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

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

 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()
 )
 GO
 
 --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 

Open in new window


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 [int] PRIMARY KEY,
 MessageType varchar(20) NULL,
 MessageContent varchar(1000) NOT NULL,
 PushDate datetime NULL,
 EngineID int  NOT NULL
 ) 
 GO
 
 CREATE PROCEDURE usp_InsertMessages 
 @MyParameter MessageQueue READONLY,
 @EngineId varchar(20)
  AS
 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
 
 GO
 
 --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 

Open in new window


 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>;

Open in new window

1
Comment
Author:apexpert
0 Comments

Featured Post

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month