Table Valued Parameters

Table Valued parameters are user defined table types and used to pass multiple rows  of data  as input to procedure or function.  

Benefits 

* we dont need many parameters

* we dont need temporary tables

* We dont need XML to pass multiple values

* pass multiple values of different data types

* Enable client to specify sort order and unique keys

* SQL 2012 onwards table valued parameters are cached for all queries.

Limits

*Have no statistics

* Must be passed as READONLY.

* Ideal for less than 1000 rows due to Initialization cost

Lets see how to use it

TRUNCATE TABLE int_data

TRUNCATE TABLE test_data

CREATE TABLE int_data

  (

     id INT

  )

CREATE TABLE test_data

  (

     id   INT,

     data VARCHAR(100)

  )

go

DECLARE @i INT

SET @i = 1

WHILE @i <= 256

  BEGIN

      INSERT INTO int_data

      VALUES      (@i)

      SET @i = @i + 1

  END

go

DECLARE @i INT

SET @i = 1

WHILE @i <= 256

  BEGIN

      INSERT INTO test_data

      VALUES      (@i,

                   Char(@i) )

      SELECT @i,

             Char(@i)

      SET @i = @i + 1

  END

go

SELECT *

FROM   int_data

SELECT *

FROM   test_data

GO

CREATE type test_table_type AS TABLE (id int)

go

CREATE PROC Test_proc @test_table TEST_TABLE_TYPE readonly

AS

  BEGIN

      SELECT t.*

      FROM   test_data t

             JOIN @test_table t1

               ON t.id = t1.id

  END

go

DECLARE @t TEST_TABLE_TYPE

DECLARE @i INT

SET @i = 97

WHILE @i <= 122

  BEGIN

      INSERT INTO @t

      VALUES      (@i )

      SET @i = @i + 1

  END

EXEC Test_proc   @t