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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<#
/* FROM SSMS */
USE AdventureWorks
GO
/* Create a CustomerList table type */
CREATE TYPE Sales.CustomerList AS TABLE 
( CustomerID INT );
GO
 
/* Create a procedure to use new table type */
CREATE PROCEDURE Sales.uspGetCustomer
    @TVP CustomerList READONLY
    AS 
    SET NOCOUNT ON
    SELECT c.*
    FROM Sales.Customer c
    JOIN @TVP t ON
    c.CustomerID = t.CustomerID;
 GO
 
/* 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)
SELECT * FROM (
	VALUES (1),(2),(3),(4),(5)
) AS v (CustomerID)
 
/* Pass the table variable data to a stored procedure. */
EXEC Sales.uspGetCustomer @CustomerTVP;
GO
#>
 
#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]
$dt.Columns.Add($Col)
 
#Add a Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 1   
$dt.Rows.Add($dr)  
 
#Add a 2nd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 2   
$dt.Rows.Add($dr)  
 
#Add a 3rd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 3   
$dt.Rows.Add($dr)  
 
#Connection and Query Info
$serverName="$env:computernamesql1" 
$databaseName='AdventureWorks' 
$query='Sales.uspGetCustomer' 
 
#Connect
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" 
$conn = new-object System.Data.SqlClient.SqlConnection $connString 
$conn.Open()
 
#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)
$conn.Close()
 
#Return results
$ds.Tables

{ 0 comments… add one }

Leave a Comment


+ four = 9

%d bloggers like this: