Use Of Table-Valued Parameter In SQL Server

Table Valued Parameter is a new feature introduced with SQL Server 2008. Table Valued Parameters help us pass multiple rows of data from a client application to SQL Server without multiple round trips. We can pass multiple rows to a stored procedure using a Table Valued Parameter.
 
Table-valued parameters must be passed as READONLY parameters to SQL routines like a stored procedures. We cannot perform DML operations like UPDATE, DELETE, or INSERT on table valued parameters.
 
Create a Table Valued Parameter
 
The following describes how to create a Table Valued Parameter. 
  1. CREATE TABLE CUSTOMER  
  2. (  
  3. CustomerId INT NOT NULL,  
  4. CustomerName VARCHAR(MAX),  
  5. Isdeleted BIT,  
  6. PRIMARY KEY (CustomerId)  
  7. )  
Step 1
 
Create a User Defined type and define the structure, as in:
  1. CREATE TYPE dbo.TableValuedTypeExample AS TABLE  
  2. (  
  3. CustomerId INT NOT NULL,  
  4. CustomerName VARCHAR(MAX),  
  5. PRIMARY KEY (CustomerId)  
  6. )  
Step 2
 
Create a Stored Procedure with a table type parameter. Here the Table Valued Parameter must be READONLY. 
  1. CREATE PROC InsertValue  
  2. (@TempTable AS dbo.TableValuedTypeExample READONLY)  
  3. AS  
  4. BEGIN  
  5. INSERT INTO CUSTOMER (CustomerId,CustomerName ,Isdeleted )  
  6. SELECT CustomerId, CustomerName, 0 AS Isdeleted FROM @TempTable  
  7. END  
Step 3. Calling Table Valued Parameter 
  1. DECLARE @MyCustomer AS dbo.TableValuedTypeExample  
  2. INSERT INTO @MyCustomer VALUES (1,'Jignesh')  
  3. INSERT INTO @MyCustomer VALUES (2,'Tejas')  
  4. INSERT INTO @MyCustomer VALUES (3,'Rakesh')  
  5. EXEC InsertValue @MyCustomer  
Advantages 
  • A Table Valued Parameters help us to reduce round-trips to the server.
  • Table Valued Parameters are strongly typed.
  • Table Valued Parameters enable us to include complex logic in a SQL subroutine like a SP.
  • Table Valued Parameters  provide a simple programming model.
 
Limitations 
  • We cannot pass a Table Valued Parameter to user-defined functions
  • It only supports UNIQUE and PRIMARY KEY constraints.
  • It is a Read-only collection in T-SQL.
  • We cannot alter the design of the Table Valued Parameter.
 
Conclusion
 
Table Valued Parameters are one of the excellent new features of SQL Server 2008. They help us to reduce round trips to the server and also reduces the use of temporary tables or passing multiple parameters in Store Procedures.