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 datatype 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 can not be passed to a stored procedure.
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() ) 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
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
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
References - View this article on My Blog , MSDN Article
Happy Coding!!! Happy Reading!!!
~Aamod
[Microsoft Certified Technology Specialist - SQL Server 2005]
User Comments