Executing a stored procedure with Powershell

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