In my previous article, I discussed how to call a stored procedure from PowerShell (PoSh) and store the results in a file. Today, I want to address a common question that arises when working with stored procedures: how to call a stored procedure with parameters.

Let’s start with a simple yet insecure way of calling a stored procedure. In the previous article, I used the Invoke-SqlCmd cmdlet to call a procedure without parameters. To add parameters, we can modify the code as follows:

$results = Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2017 -Query "[dbo].[uspGetBillOfMaterials] @StartProductID = 749, @CheckDate = '2010-05-26'"

While this approach works, it lacks flexibility. What if we want to make this into a function that users can call or pass in parameters from the command line? We need a better solution.

A better way to declare parameters is by using .NET classes to construct the code. This approach is more secure and avoids the risk of SQL injection. Here’s how we can do it:

$Server = "localhost"
$Database = "AdventureWorks2017"
$SqlConn = New-Object System.Data.SqlClient.SqlConnection("Server = $Server; Database = $Database; Integrated Security = True;")
$SqlConn.Open()

$cmd = $SqlConn.CreateCommand()
$cmd.CommandType = 'StoredProcedure'
$cmd.CommandText = 'dbo.uspGetBillOfMaterials'

$p1 = $cmd.Parameters.Add('@StartProductID',[int])
$p1.ParameterDirection.Input
$p1.Value = 749

$p2 = $cmd.Parameters.Add('@CheckDate',[DateTime])
$p2.ParameterDirection.Input
$p2.Value = '2010-05-26'

$results = $cmd.ExecuteReader()

$SqlConn.Close()

$results | Export-Csv -LiteralPath "sproc.csv" -NoTypeInformation

In this code, we establish a connection to the SQL Server, create a command object, and set its properties to specify the stored procedure we want to call. We then add parameters to the command object and assign their values. Finally, we execute the command and retrieve the results.

By using this approach, we can ensure the security of our code and easily reuse it in different scenarios. It’s a more robust solution compared to building a string from parameter values.

In conclusion, there are two ways to call stored procedures with parameters in SQL Server. The first approach involves adding parameters as part of the batch string, but it is not recommended due to security risks. The second approach, which uses .NET objects, is more secure and provides better flexibility. I recommend using the second approach in your PowerShell scripts.

I hope this article helps you in your SQL Server development. If you have any questions or would like to learn more about a specific topic, please leave a comment below.