PowerShell get “Return Value” from StoredProcedure ExecuteNonQuery

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. 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

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:

The Power Shell Script is as follows:

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;

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

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.

3 thoughts on “PowerShell get “Return Value” from StoredProcedure ExecuteNonQuery

  1. Hi, many thanks for this guide! I managed to fix your return problem.

    Just add “>> $null” to each Add & AddWithValue

    ex.

    $SqlCmd.Parameters.AddWithValue(‘@moduleTag’,$moduleTag) >> $null
    $SqlCmd.Parameters.AddWithValue(‘@modLog_IsDebug’,$isDebug) >> $null
    $SqlCmd.Parameters.Add(“@ReturnValue”, [System.Data.SqlDbType]”Int”) >> $null

    this way it will just return the [int]Value we want 🙂

    source that hinted me: https://stackoverflow.com/questions/30899586/how-to-get-stored-procedure-output-parameter-into-variable-using-powershell

  2. Excellent post, this really helped me.
    Also the comment of Gian Luca Mutinelli was very useful, this was exactly what I was looking for.
    Thanks.

Leave a Reply to Jonathan Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.