Table-Valued Parameter in SQL Server 2008

Introduction

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.

CREATE TABLE CUSTOMER
(
      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)
AS
BEGIN
      INSERT INTO CUSTOMER (CustomerId,CustomerName ,Isdeleted )
      SELECT CustomerId, CustomerName, 0 AS Isdeleted FROM @TempTable
END

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

Advantage

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

Limitation

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