Creating and testing Table type Value Parameter in SQL

 Table Valued Parameter is a new feature introduced with SQL Server 2008. Table Valued Parameters help to pass multiple rows of data from a client application to SQL Server without multiple round trips. Using a Table Valued Parameter we can pass multiple rows to a stored procedure.

Create Table Type

  Here I using list of country to explain

Create Type CountryType as Table

(CountryName varchar(100))

Create a Procedure to select data

Create Proc TestTableType

@Country CountryType Readonly

as

begin

select * from @Country

end

Passing table type parameter to store procedure

DECLARE @Country AS CountryType

 

INSERT INTO @Country

VALUES (  'India'

       )

INSERT INTO @Country

VALUES (  'China'

       )

INSERT INTO @Country

VALUES ( 'US'

       )

exec TestTableType  @Country