The scenario is that from PowerShell you want to do the following:

1. Execute a stored Procedure
2. Check the Return Code from the stored procedure call.
3. Perform conditional logic based upon whether the stored procedure succeeded or failed

The following will show you how to:

1. Create a PowerShell function to execute a stored procedure.
2. Call this stored procedure and extract the return code into an integer variable
3. Apply conditional logic based upon the return code

I struggled with getting this to work and did not find any exact examples that explained it exactly as I wanted.

Environment Setup

1. 1. 1. SQL Server / Express – I am running SQL Sever 2008 R2 Express on the local host
      2. Create an empty Database called “TestDB”
      3. PowerShell configured with Remote-Signed Execution Policy. If you don’t know how to do this go here Set PowerShell Execution Policy

StoredProcedure

Create a stored procedure as follows on your TestDB

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prcTest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[prcTest]
GO

Create Procedure prcTest
	@value int
as
begin
	Declare @return float

	BEGIN TRY

		-- could return divide by 0 error if value = 0
		set @return = 1 / @value;

		Return 1 -- success

	END TRY
	BEGIN CATCH
		-- will return 0 when there has been a divide by zero error
		return 0
	END CATCH
end

This procedure will accept a single integer parameter and divide by it. If zero is passed in the procedure will fail with a divide by zero error and return the return code of 0.
Any other number will succeed.

• 1 = Success; and
• 0 = Error

(NOTE: I specifically reversed the normal codes returned from Stored Procedures because if there is a connection problem and the following powershell script cannot execute it will return 0 which needs to be treated as a failure, so 1 must be the success code)

PowerShellScript

Create a file called test.ps1 in your c:\Scripts directory.

You can run this from the PowerShell ISE or you can create a .bat file and past this line in it:

PowerShell.exe -noexit c:\scripts\test.ps1

The Power Shell Script is as follows:

function ConnectionString()
{
    return "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=.\SQLExpress";
}

function executeStoredProcedure($value)
{

    $connection = ConnectionString;
    $query = "prcTest";

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection
    $sqlConnection.Open() 

    $sqlCmd = new-object System.Data.SqlClient.SqlCommand("$query", $sqlConnection) 

    $sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure" 

    $sqlCmd.Parameters.AddWithValue("@value", $value)

    $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
    $sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue" 

    $sqlCmd.ExecuteNonQuery() | out-null
    $sqlConnection.Close() 

    [int]$sqlCmd.Parameters["@ReturnValue"].Value

}

# Test 1 - Should fail
#======================================
Write-Host "====================="
Write-Host "TEST 1"
Write-Host "====================="
$out = executeStoredProcedure(0)
Write-Host $out
$returnValue = $out[2]
Write-Host $returnValue

if($returnValue -eq $null -or $returnValue -eq 0)
{
    Write-Host "An Error Occured"
}
else
{
    Write-Host "Success"
}

# Test 2 success
# ---------------
# removed the Write-Host lines
#======================================
Write-Host "====================="
Write-Host "TEST 2"
Write-Host "====================="
$out = executeStoredProcedure(1)
$returnValue = $out[2]

if($returnValue -eq $null -or $returnValue -eq 0)
{
    Write-Host "An Error occurred"
}
else
{
    Write-Host "Success"
}

I will talk you through a few of the interesting points of above.

ConnectionString Fuction
I do this so I can have my connection string centralised in my script and that way if
I need to change the SQL Server credentials I am using I only need to make the change in one place

[int]$sqlCmd.Parameters[“@ReturnValue”].Value
This gets the “ReturnValue” from the stored procedure and then casts it to an integer.

Note: I did try code like;

$rc = [int]$sqlCmd.Parameters["@ReturnValue"].Value
return $rc

But it made no difference to the output of the function.

$returnValue = $out[2]
I will confess I don’t now a great deal about PowerShell but I have worked through this problem to get a working solution.
If you execute it and look at the output from the “Write-Host $out” you will see it appears to be an array type object and it prints as

@value @ReturnValue 1

I figured this is a zero based array so I just accessed the value in position 3 i.e. $out[2], which is already of type “int” due to the cast in the function.

Conditional Logic
So now I have the integer return value in a variable $returnValue I can use it in my conditional logic. If the value was null or == 0 then it must be in error else success.

Implementing this means you can execute a stored procedure and if it fails , you can cancel any further processing in the Script.

Nice 😉

Update – Oct 2012

I recently had to use this code again and I found that the best solution is to have the stored procedure return “non-zero” values, eg 1 for success, -1 for failure.
The issue is that the return code from the “executeStoredProcedure” call will be zero in certain catastrophic conditions. For example, break your connection string on purpose and the stored procedure will never get executed and the return code from “executeStoredProcedure” is zero.

By using non-zero return codes you now have 3 possible states, 1 – success, -1 failure, 0 – catastrophic failure.
I extended my code to use “blat.exe” to send admin emails on return code zero so that if my powershell script failed to run someone is notified.