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




No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts