Best Way For Table Type Parameters In SQL Server

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 Stored Procedure. In this post, we discuss how to use table type parameters in SQL Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to SQL Server, then we use to model the input data as XML and pass it to the stored procedure and in 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 Type that can be reused in multiple stored procedures as 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 Define 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 Stored Procedure With table type 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