Table Valued parameter to Stored Procedure

SQL Server 2008 provides us ability to pass batch of parameter in the form of table valued parameter to stored proc. Earlier we could pass batch of parameter to stored proc with the help of XML only. XML construction and parsing is little bit cumbersome, now SQL Server 2008 overcome with this limitation.

Table-valued parameters are declared by using user-defined table types. Table-valued parameters are like array.

Steps to pass table valued parameter to Store Procedure:-

1)      Define the type as a table and its structure

2)      Create Proc having variable as above(in step 1) created table type as input param

3)      Declare a variable of above created table type

4)      Insert the values to variable

5)      Pass the variable to Stored Proc

Now we get more clarity on passing the Table-valued parameter to Stored Procedure with the help of below example:-

Define the type as a table and its structure:-

CREATE TYPE tbl_TableType AS TABLE (id INT

, Name VARCHAR (50)

    )

Now we can check the existence of above create user defined table type with the help of below query:-

SELECT * FROM sys.table_types

Create Proc having variable as above(in step 1) created table type as input parameter :-

CREATE PROCEDURE proc_TableValueParam ( @p_TableVariable  tbl_TableType READONLY )
AS
BEGIN
        SELECT  *
        FROM @p_TableVariable 
END
GO

Declare a variable of above created table type:-

DECLARE @v_table  tbl_TableType

Insert the values to variable:-

INSERT  INTO @v_table
VALUES  ( 1, ‘Aa’ )
                ,  ( 2, ‘Bb’ )
                ,  ( 3, ‘Cc’ )

Pass the variable to Stored Proc:-

EXEC dbo. proc_TableValueParam @ v_table

Result:-

id	name
1	Aa
2	Bb
3	Cc

reachable : agrawals1307@gmail.com
facebook page