Introduction
I had some troubles finding a good example on how to execute a Stored procedure in Powershell and so I made a couple of examples my own and this blog is the result of some experimentation.
First, let's start with an simple example. Execute a stroed procedure without a parameters. I've created a sample database, a sample table and a sample stored procedure.
DROP TABLE IF EXISTS TestSP GO CREATE TABLE TestSP ( id int identity(1,1), Name varchar(50), Insertdate datetime DEFAULT GETDATE() ) GO CREATE PROCEDURE InsertTestSP AS BEGIN INSERT INTO TestSP (Name) VALUES ('HENNIE') END EXEC InsertTestSP GO SELECT * FROM TestSP
Resulting in a record in the table.
Execute a simple stored procedure
Now let's find out if we can execute the stored procedure from Powershell.cls
Write-Host "Script started..."
# General settings
$date = Get-Date
$dateString = $date.ToString("yyyyMMddHHmmss")
# SQL Server settings
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=TestSPWithPowershell;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "InsertTestSP"
$SqlCmd.Connection = $SqlConnection
$sqlConnection.Open()
$Result = $SqlCmd.ExecuteNonQuery()
$sqlConnection.Close()
And this results in a record in the table.
Execute Stored procedure with a parameter
Now we add a parameter to the stored procedure. First change the stored procedure a bit in order to have a proper testsituation : Parameter added and value is inserted in the table.DROP PROCEDURE IF EXISTS InsertTestSP
GO
CREATE PROCEDURE InsertTestSP @name as varchar(50) AS
BEGIN
INSERT INTO TestSP (Name) VALUES (@name)
END
EXEC InsertTestSP 'Jack'
GO
SELECT * FROM TestSP
Resulting in :
So that works too. The record with the name 'Jack' is properly inserted into the table with the stored procedure. Let's find out if we can do that with Powershell too.
cls
Write-Host "Script started..."
# General settings
$date = Get-Date
$dateString = $date.ToString("yyyyMMddHHmmss")
# SQL Server settings
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=TestSPWithPowershell;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "InsertTestSP @name"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.Parameters.Add("@name",[system.data.SqlDbType]::VarChar) | out-Null
$SqlCmd.Parameters['@name'].Direction = [system.data.ParameterDirection]::Input
$SqlCmd.Parameters['@name'].value = "Jean"
$sqlConnection.Open()
$Result = $SqlCmd.ExecuteNonQuery()
$sqlConnection.Close()
And this results in the following table. And again this works too.
Just a blogpost on how to execute a stored procedure from Powershell. Just enough for my situation at my project.
Hennie
