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
![](/ArticleFiles/1891/image001.gif)
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
![](/ArticleFiles/1891/image002.gif)
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