Printed 2017/02/26 09:28PM

Table-Valued Parameter Example


I wanted show someone how to use table-valued parameters available in SQL Server 2008 and higher. The main use case of table-valued parameters is for sending a list or array of items as parameter to a SQL Server stored procedure or function. This is more efficient than parsing strings or XML on the SQL Server side. I couldn’t seem to find a complete example of table-valued parameters in the SQL Server documentation. The SQL Server docs only shows the T-SQL portion of the code and not the ADO.NET. I think its difficult to see how you would use this feature without having both the T-SQL and .NET code shown together so, here’s a simple T-SQL and Powershell script demonstrating table-valued parameters:

USE AdventureWorks
/* Create a CustomerList table type */
CREATE TYPE Sales.CustomerList AS TABLE 
( CustomerID INT );

/* Create a procedure to use new table type */
CREATE PROCEDURE Sales.uspGetCustomer
    @TVP CustomerList READONLY
    SELECT c.*
    FROM Sales.Customer c
    JOIN @TVP t ON
    c.CustomerID = t.CustomerID;

/* Test type and procedure in SSMS */

/* Declare a variable that references the type. */
DECLARE @CustomerTVP AS Sales.CustomerList;

/* Add data to the table variable. */
INSERT INTO @CustomerTVP (CustomerID)
	VALUES (1),(2),(3),(4),(5)
) AS v (CustomerID)

/* Pass the table variable data to a stored procedure. */
EXEC Sales.uspGetCustomer @CustomerTVP;

#FROM Powershell
#Create an ADO.NET DataTable matching the CustomerList Table Type:
$dt = new-object Data.datatable  
$col =  new-object Data.DataColumn  
$col.ColumnName = 'CustomerID'  
$col.DataType = [Int32]

#Add a Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 1   

#Add a 2nd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 2   

#Add a 3rd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 3   

#Connection and Query Info

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" 
$conn = new-object System.Data.SqlClient.SqlConnection $connString 

#Create Sqlcommand type and params
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText= $query
$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)
$cmd.Parameters["@TVP"].Value = $dt

#Create and fill dataset
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$null = $da.fill($ds)

#Return results

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.