Introduction
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.
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. 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.
Conclusion
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.