|
The long awaited feature of passing table valued parameter (TVP)has been provided by SQL 2008. I believe most of the developer in one or other scenario has to pass array of values to stored procedure they never could prior to SQL 2008. Before sql 2008, the only way to do is that passing value as XML or comma seperated both were complicated or multiple trip to database to save the data.
The advantage of using table valued parameter is it reduces the round trip and it is less complicated
The disadvantage of using table valued parameter is that it can not be modified because it has to be passed as READONLY.
Let's see how we can pas TVP to stored procedure.
Step 1: Create a table TestTVP with name and age as column.
CREATE TABLE TestTVP(name VARCHAR(50), age INT)
Step 2: Creat table type TestTVPType
CREATE TYPE TestTVPType AS TABLE
(
name VARCHAR(10),
age INT
)
Step 3: Create stored procedure sp_testtvp_i which accepts table type parameter.
CREATE PROCEDURE sp_testtvp_i(@testtvptype TestTVPType READONLY)
AS
INSERT INTO TestTVP(name,age)
SELECT name,age FROM @testtvptype;
GO
Step 4: Declare TestTVPType table variable and insert data into the TestTVPType variable
DECLARE @testtvptype AS TestTVPType
INSERT INTO @testtvptype(name,age) VALUES('ABC',20),('PQR',30)
Step 6: Execute sp_testtvp_i stored procedure by passing TestTVPType table variable
EXEC sp_testtvp_i @testtvptype;
GO
This ends the article.
|