Use Of Table-Valued Parameter In SQL Server


Table Valued Parameter is a new feature introduced with SQL Server 2008. In this article, we will learn about Table Valued parameters in SQL Server.

What is Table Valued Parameter in SQL Server?

Table Valued Parameters are one of the excellent new features of SQL Server. Table Valued Parameters help us pass multiple rows of data from a client application to SQL Server without numerous round trips. Using a Table Valued Parameter, we can pass multiple rows to a stored procedure.

Table-valued parameters must be passed as READONLY parameters to SQL routines like stored procedures. We cannot perform DML operations like UPDATE, DELETE, or INSERT on table-valued parameters.

How do you create a Table Valued Parameter?

The following describes how to create a Table Valued Parameter. 

CustomerId INT NOT NULL,  
CustomerName VARCHAR(MAX),  
Isdeleted BIT,  
PRIMARY KEY (CustomerId)  

Step 1

Create a User-Defined type and define the structure, as in.

CREATE TYPE dbo.TableValuedTypeExample AS TABLE  
CustomerId INT NOT NULL,  
CustomerName VARCHAR(MAX),  
PRIMARY KEY (CustomerId)  

Step 2

Create a Stored Procedure with a table-type parameter. Here the Table Valued Parameter must be READONLY. 

CREATE PROC InsertValue  
(@TempTable AS dbo.TableValuedTypeExample READONLY)  
INSERT INTO CUSTOMER (CustomerId,CustomerName ,Isdeleted )  
SELECT CustomerId, CustomerName, 0 AS Isdeleted FROM @TempTable  

Step 3. Calling Table Valued Parameter 

DECLARE @MyCustomer AS dbo.TableValuedTypeExample  
INSERT INTO @MyCustomer VALUES (1,'Jignesh')  
INSERT INTO @MyCustomer VALUES (2,'Tejas')  
INSERT INTO @MyCustomer VALUES (3,'Rakesh')  
EXEC InsertValue @MyCustomer  

What are the advantages of Table Valued Parameters in SQL Server?

  • 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 an SP.
  • Table Valued Parameters provide a simple programming model.

What are the limitations of Table Valued Parameters in SQL Server?

  • 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.


This article taught us about Table valued parameters in SQL Server. They help us reduce round trips to the server and reduce the use of temporary tables or passing multiple parameters in Stored Procedures in SQL Server.

Similar Articles