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 [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 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.
|