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