In my first article, I covered how I can call a stored procedure from PowerShell (PoSh) and storing the results in a file. Someone asked a question about how to call a stored procedure with a parameter, which is a good question to ask, and a natural extension of what I wrote. Here I'm going to cover a couple ways to call stored procedures with parameters.
The Simple Yet Insecure Way
Let's start with a simple way of calling a stored procedure based on the code in the previous article. This will essentially involve using some PoSh string substitution. For this, I'll use some code from the first article as shell. However, first, let's get the database setup.
Finding a Sproc in AdventureWorks
I restored a copy of AdventureWorks2017 on my development instance. In here, there are a few sprocs, one of which is [dbo].[uspGetBillOfMaterials]. This takes two parameters, a product ID and a date. With a little experimenting, I found that using a product of 749 and a date of May 26, 2010 gets me some data. I can see this with a scripted execution in SSMS.
I'll want to call this same stored procedure from PoSh, so let's look at that.
Adding Parameters in the String
In the previous article, I used the Invoke-SqlCmd cmdlet to call a procedure. This is the code I wrote before:
$results = Invoke-Sqlcmd -ServerInstance localhost -Database db2 -Query "CustomerSales"
We can change this to our new sproc, which is easy, but what about the parameters? We can certainly set up a call in SSMS like this:
EXECUTE [dbo].[uspGetBillOfMaterials] @StartProductID = 749, @CheckDate = '2010-05-26'
Let's put that in our PoSh code. When we do that, we get something like this:
$results = Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2017 -Query "[dbo].[uspGetBillOfMaterials] @StartProductID = 749, @CheckDate = '2010-05-26'"
Note, I changed the database here as well. This does work, as you can see in the screenshot below. When I look at the $results variable, I see my results.
That works, but it's not flexible. What if I want to make this into a function a user can call, or pass in a parameter to this script from the command line. I need a better solution.
I can start by assigning the parameter values to variables. I'll use this PoSh code.
$productid = 729 $checkdate = "2010-05-26"
Now I want to include them in my call. To do this, I can add the variables in there with this code:
$a = "[dbo].[uspGetBillOfMaterials] @StartProductID = $productid, @CheckDate = '$checkdate'"
This is for testing, but when a user looks at this variable, they'll see the result in this image. The values for the parameters get substituted into the strings.
OK, this is useful. Let's use this in the script and come up with this code.
$productid = 749 $checkdate = "2010-05-26" $results = Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2017 -Query "[dbo].[uspGetBillOfMaterials] @StartProductID = $productid, @CheckDate = '$checkdate'" $results | Export-Csv -Path "sproc.csv" -NoTypeInformation
When I run this, I get a csv with these contents.
This looks like what I got from SSMS. This could be improved by making a function, or accepting parameters in your scripts with the $args variable. I'll look at those ideas in a future article.
A Better Way to Declare Parameters
The problem with doing things this way is that you are essentially building a string out of parameter values. Doing this in .NET code, or really any code, has been a recipe for SQL Injection. I would like to avoid this, so that means I want to use a more secure method of specifying specific parameter values, not building a string from other strings.
To do this, I need to use some of the .NET classes to construct my code. This feels complex, but it's not that hard. Once you have the code, you can use this over and over. Here's how I'd do it.
First, I need to get a connection and open it. I'll do that, but set a couple variables.
$Server = "localhost"
$Database = "AdventureWorks2017"
$SqlSqlConn = New-Object System.Data.SqlClient.SqlSqlConnection("Server = $Server; Database = $Database; Integrated Security = True;")
$SqlConn.Open()Notice I'm using the System.Data.SqlClient namespace here, which has a lot of the objects I need. I'll use that to create the stored procedure object. This comes from the connection object.
$cmd = $SqlConn.CreateCommand() $cmd.CommandType = 'StoredProcedure' $cmd.CommandText = 'dbo.uspGetBillOfMaterials'
Once I have this, I can start to add parameters. I'll assign variables directly here, but I could use the parameters I showed in the previous section.
$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'There are a lot of things I can do with parameters. Notice that I set the direction here. I could have output parameters if I wanted.
Once I have this,I need a place to put the results. I'll use a DataTable, which is another type of object. I'll create that and take the object back from the stored procedure call and then put that in the table.
I can call the sproc. If I put together all the code, I have this:
$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()
$dt = New-Object System.Data.DataTable
$dt.Load($results)
$SqlConn.Close()
$dt | Export-Csv -LiteralPath "C:\Users\frank\Documents\sproc.txt" -NoTypeInformationWhen I run this, I get a csv in my folder, and I can open that to see the results.
Notice that I do close my connection at the end, which is always a good idea.
Parameters with Sprocs Two Ways
I've shown you two ways to call stored procedures with parameters in this article. One just adds parameters as part of the batch string, which I do not recommend. The second way uses .NET objects and looks complex, but it is likely more secure. I would use this second way.
I could have shown sp_executesql() and called my procedure from there, but that feels like more work to me. It's much easier to use the pattern I know with .NET objects.
I hope this helps you write some PoSh code that's useful. I'll delve into parameters and calling scripts in another article. If there's something else you'd like to learn, ask in the comments for this article.
References Used
- https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
- https://docs.microsoft.com/en-us/powershell/scripting/learn/deep-dives/everything-about-string-substitutions?view=powershell-7
- https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types
- https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient?view=dotnet-plat-ext-3.1
- https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype?view=dotnet-plat-ext-3.1
