Table Valued Parameters-TVP's

Table Valued Parameter in sql server gives us another choice for treating a set of rows as single entity that we can query or Join against.

The real power of TVP lies in the ability to pass entire table as a single parameter from client to server and between your T-SQL stored procedure and user defined functions. Table variables and temporary tables cannot be passed as parameters, CTE's are limited in scope.

Syntax:

CREATE TYPE <TVPNAME> as Table

(

<param1>,

<param2>

……..

)

TVP’s are displayed in Management Studio Object Explorer in the User-Defined Table node beneath Programmability, Types   as shown in below figure…

Let’s Look at the below example:

Creating a TVP:

CREATE TYPE LocationUdt1 as Table

(

LocationName Varchar(50),

LocationID int

)

Passing TVP as parameter to Stored Procedure

CREATE PROCEDURE InsertLocation

(

@tvp LocationUdt1 READONLY

)

AS

      Insert Into Location(Name,ID)

      Select * from @tvp

Declaring and assigning values to TVP

Calling a Stored procedure:

Declare @location as LocationUdt1

Insert Into @location values ('Hyderaba',1)

Insert Into @location values ('Nizamabad',2)

Exec  InsertLocation @location

Limitation of TVP's
  1. TVP’s are read only, they cannot be used to return data.     
 2.  OUTPUT keyword cannot be used     
 3.  Cannot ALTER TVP 
4. Indexing is limited,with support only for Primary and Unique constraints.      
5.  Statistics on TVP are not maintained by SQL server.