Wednesday, August 17, 2011

How to pass Table variable to a stored procedure with an example?


/*The first step in setting up the use of table valued parameters is creating a specific table type;this is necessary so that the structure of the table is defined in the database engine. This allows you to define the type of table and reuse it as needed in your procedure code.*/

IF OBJECT_ID('TransactionHistory')>0
DROP TABLE TransactionHistory;
CREATE TABLE [dbo].TransactionHistory
(
[TranID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[TranDate] [datetime] NULL,
[TranPrice] [money] NULL
)
GO


/*This code creates the TransactionHistoryTabletype table type definition.*/




CREATE TYPE TransactionHistoryTableType AS TABLE(
[Product] [varchar](10) NULL,
[TranDate] [datetime] NULL,
[TranPrice] [money] NULL
)





CREATE PROCEDURE usp_InsertCareerProducts
( @TableVariable TransactionHistoryTableType READONLY )AS
BEGIN
INSERT INTO TransactionHistory
(Product, [TranDate], [TranPrice])SELECT Product, [TranDate], [TranPrice]
FROM @TableVariable
WHERE Product = 't-mobile'

END

/*The following script inserts data into my @TranTable table variable */


BEGIN
DECLARE @TranTable AS TransactionHistoryTableType
INSERT INTO @TranTable(Product, TranDate, TranPrice)
VALUES('TMobile',  GETDATE(),50 )
INSERT INTO @TranTable(Product, TranDate, TranPrice)
VALUES('Verizon',  GETDATE(),60)
INSERT INTO @TranTable(Product, TranDate, TranPrice)
VALUES('AT&T',  GETDATE(),70) 
 /*Now execute the store procedure */

EXECUTE usp_InsertCareerProducts @TableVariable = @TranTable


END



Table Parameter Restrictions

You must use the READONLY clause when passing in the table valued variable into the procedure.Data in the table variable cannot be modified you can use the data in the table for any other operation
Also, you cannot use table variables as OUTPUT parameters you can only use table variables as input parameters.


No comments:

Post a Comment