SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

<# 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

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.

Comments

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

Loading comments...