Calling a Stored Procedure with Output Parameters

• imani_technology

  SSCrazy Eights

  Points: 8200

  More actions

  September 21, 2016 at 12:20 pm

  #313362

  I have another PowerShell newbie question. I want to run a stored procedure from a PowerShell script and receive the resulting output parameter values. So basically, I would like to run the equivalent of the code below, but also receive the results. How might i do this? I'm sure Invoke-Sqlcmd has something to do with it, but I have no idea how to implement.

  USE [msdb]

  GO

  DECLARE@return_value int,

  @job_name sysname,

  @job_id uniqueidentifier,

  @last_run_date int,

  @last_run_time int,

  @job_status int,

  @job_stat_desc varchar(100)

  SELECT@job_name = [NOTE: PowerShell script needs to pass this in.]

  EXEC@return_value = [dbo].[sp_RunSQLAgentJob]

  @job_name = @job_name OUTPUT,

  @job_id = @job_id OUTPUT,

  @last_run_date = @last_run_date OUTPUT,

  @last_run_time = @last_run_time OUTPUT,

  @job_status = @job_status OUTPUT,

  @job_stat_desc = @job_stat_desc OUTPUT

  SELECT@job_name as N'@job_name',

  @job_id as N'@job_id',

  @last_run_date as N'@last_run_date',

  @last_run_time as N'@last_run_time',

  @job_status as N'@job_status',

  @job_stat_desc as N'@job_stat_desc'

  SELECT'Return Value' = @return_value

  GO

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  September 22, 2016 at 2:08 am

  #1902363

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