--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
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
GRANT EXECUTE ON TYPE::dbo.MessageQueue TO <User Name>;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)