Understanding Table-Valued Parameters In SQL Server For Better Performance

This is a new feature introduced in SQL Server 2008. Table-valued or table type parameters in SQL Server provide an option for the Client Applications to pass multiple rows of Data to a Stored Procedure. In this post, we discuss how to use table-valued parameters in SQL Server.

Before this, if we needed to pass multiple rows of Data from a client application to SQL Server, then we used to model the input data as XML and pass it to the stored procedure, and in the Stored Procedure convert this XML to a table variable/temporary table. In this article, we will not only go over this Table-Valued Parameter we will also understand how to call the Stored Procedure with Table-Valued Parameter from SQL Server and C# .NET Code.

First, we need to create a User-Defined Table-Valued function that can be reused in multiple stored procedures as an input table parameter data type.


CREATE TYPE dbo.CustomerTableType AS TABLE
( 
 [CustomerID] INT,
 [Name]  VARCHAR(50)
)
GO

CREATE TYPE dbo.CustomerTableType AS TABLE

(

[CustomerID] INT,

[Name] VARCHAR(50)

)

Now, let us create a simple stored procedure that takes CustomerType User-Defined Table Type, which we have created previously.


CREATE PROCEDURE dbo.GetCustomerDetails
(
 @Customers AS dbo.CustomerTableType READONLY
)
AS
BEGIN
 SET NOCOUNT ON

 SELECT *
 FROM @Customers
END

CREATE PROCEDURE dbo.GetCustomerDetails

(

@Customers AS dbo.CustomerTableType READONLY

)

BEGIN

SET NOCOUNT ON

SELECT *

FROM @Customers

END

Using a Stored Procedure with table-valued parameters in SQL Server.


Declare @CustomerDetails As dbo.CustomerTableType
Insert Into @CustomerDetails Values(1,'TechAid24'),
         (2,'Technical'),
         (3,'Blog Site') 

Exec dbo.GetCustomerDetails @CustomerDetails
GO
Result:
CustomerID   Name
----------- ------------
1           TechAid24
2           Technical
3           Blog Site