Home > DeveloperSection > Articles > Introduction of Table-Valued Parameter

Introduction of Table-Valued Parameter


Database Database 
Ratings:
0 Comment(s)
 3257  View(s)
Rate this:

Introduction of Table-Valued Parameter

In this article, I will explain the introduction of Table-Valued Parameter.  When we have to pass multiple rows of data from SQL Server the developers either have options either to send one row at a time or come up with other workarounds to meet requirements like using XML data type to pass data which is a bit complex and tedious to use.  Also there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, but the data still cannot be passed to a stored procedure.

Table-Valued Parameters:

SQL Server 2008 Provides a New Feature Called Table-Valued Parameters; it provides us to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

To perform this task, first of all we 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()

 )

 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 

 

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

 

 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 

 

In order to use the user defined data type, user must have execute permission on this type. If user doesn't have executed 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.


Don't want to miss updates? Please click the below button!

Follow MindStick