Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

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:computername\sql1" 
$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

Comments

Leave a comment on the original post [sev17.com, opens in a new window]

Loading comments...