Table-Valued Parameter in SQL Server 2008


Table Valued Parameter is a new feature introduced with SQL Server 2008. Table Valued Parameter helps us to 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 procedure. We cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter.

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

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

Step 3

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


  • A Table Valued Parameter helps us to reduce round-trips to the server.
  • Table Valued Parameters are strongly typed.
  • It enables us to include complex logic in a SQL subroutine like a SP.
  • It provides a simple programming model.


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


Table Valued Parameters are one of the excellent new features of SQL Server 2008. It helps us to reduce round trips to the server and also reduces the use of temporary tables or so many parameters to Store Procedures.

Up Next
    Ebook Download
    View all
    View all