Home Forums Programming Powershell Calling a Stored Procedure with Output Parameters RE: Calling a Stored Procedure with Output Parameters

  • I didn't know off of the top of my head so I used Bing.

    The following is from http://stackoverflow.com/questions/30899586/how-to-get-stored-procedure-output-parameter-into-variable-using-powershell:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Server=myserver;Database=mydb;Integrated Security=True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = "testsp3"

    $SqlCmd.Connection = $SqlConnection

    $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'; <-- Missing

    $outParameter = new-object System.Data.SqlClient.SqlParameter;

    $outParameter.ParameterName = "@answer";

    $outParameter.Direction = [System.Data.ParameterDirection]'Output';

    $outParameter.DbType = [System.Data.DbType]'String';

    $outParameter.Size = 2500;

    $SqlCmd.Parameters.Add($outParameter) >> $null;

    $SqlConnection.Open();

    $result = $SqlCmd.ExecuteNonQuery();

    $truth = $SqlCmd.Parameters["@answer"].Value;

    $SqlConnection.Close();

    $truth;

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!