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
