Home > Code > PowerShell get “Return Value” from StoredProcedure ExecuteNonQuery

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

    
    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:
    [bat]
    PowerShell.exe -noexit c:\scripts\test.ps1
    [/bat]

    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.

    VN:F [1.9.22_1171]
    Rating: 10.0/10 (1 vote cast)
    PowerShell get "Return Value" from StoredProcedure ExecuteNonQuery, 10.0 out of 10 based on 1 rating

Categories: Code Tags:
  1. Gian Luca Mutinelli
    August 18th, 2017 at 01:04 | #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

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  1. No trackbacks yet.