Introduction
Microsoft SQL Server 2008 has been upgraded to accept table values as parameters for the Stored procedures(SPs). Once a table valued parameter(TVP) is passed as parameter to SP, it can be used like any table but we cannot modify the parameter as it is readonly. Table valued parameters can be used to have multiple values as parameters for the SP, which was done earlier using one of the following ways:
- Using a series of multiple parameters
- Using delimited
string
or XML documents and pass those text values to SP - Creating a series of individual SQL statements that affect multiple rows.
Using the Code
TVPs are defined using the user-defined table types. The table type
is used to describe the structure of the TVP. We can see that the TVPs are strongly typed.
Using a Table value parameter:
- Create a
Table Type
and define the table
structure:
Create type CustomerType as Table
(
CustomerID INT
);
GO
- Declare a SP that has a parameter of
table type
:
CREATE PROCEDURE GetAllCustomer @CustomerIDs_TVP CustomerType READONLY
AS
SELECT CustomerID
,CustomerName
FROM Customer
INNER JOIN @CustomerIDs_TVP ON Customer.CustomerID = @CustomerIDs_TVP.CustomerID
GO
- Using the TVP as parameter with the SP:
DECLARE @CustomerIDs_TVP AS CustomerType
INSERT INTO @CustomerIDs_TVP (CustomerID) VALUES (1),(2),(3),(4),(5)
EXEC GetAllCustomer @CustomerIDs_TVP;
GO
We can see the output of the above executed query is all the customers
from Customer
table having customerID
s 1, 2, 3, 4, 5.
Using TVP in C# .NET
DataTable tableCustomerIds = new DataTable();
tableCustomerIds.Columns.Add("CustomerID", typeof(int));
tableCustomerIds.Rows.Add(1);
tableCustomerIds.Rows.Add(2);
tableCustomerIds.Rows.Add(3);
tableCustomerIds.Rows.Add(4);
tableCustomerIds.Rows.Add(5);
using (SqlConnection conn = new SqlConnection("Server=localhost;Database=MyDB;Trusted_Connection=True;"))
{
SqlCommand cmd = new SqlCommand("GetAllCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@CustomerIDs_TVP", tableCustomerIds);
cmd.CommandTimeout = 0;
sqlParam.SqlDbType = SqlDbType.Structured;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet sptable = new DataSet();
adapter.Fill(sptable);
}
This was all about the table
values parameters in SQL 2008 server. Please let me know your thoughts about the tip.