CODEWITHSUNDEEP
sql-serverpowershellstored-procedures
skyline01
skyline01

Reputation: 2101

How to get the value of a stored procedure OUTPUT variable

I have a simple SQL Server stored procedure that accepts an input parameter and sets an output parameter. The essence of the stored procedure is as follows:

CREATE PROC SomeDB.dbo.SomeProc
    @input varchar(255),
    @output int OUTPUT
AS
    IF @input = 'a'
    BEGIN
        SET @output = 0;
    END
    ELSE
    BEGIN
        SET @output = 1;
    END
    ;
GO

I want to get the value of the @output variable in PowerShell. If I were to do this in SSMS, I would do something like:

DECLARE @out int;
EXEC SomeDB.dbo.SomeProc 'a', @out OUTPUT;
PRINT @out;

But, I don't think it's that straightforward in PowerShell.

Here is my script so far in PowerShell (which I know is incomplete):

$ErrorActionPreference = "Stop"

$server = "some_IP_address"
$db = "SomeDB"
$input = "'a'"
$query = "EXEC dbo.SomeProc $input;"

try {
  Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $query -QueryTimeout 60000
} catch {
  Write-Host "stored proc error"
}

How do I get the value of the @output variable in PowerShell?

Upvotes: 1

Views: 2552

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200273

Something like this might work:

$cn = New-Object Data.SqlClient.SqlConnection
$cn.ConnectionString = '...'
$cmd = New-Object Data.SqlClient.SqlCommand
$cmd.CommandText = 'dbo.SomeProc'
$cmd.Connection = $cn
$cmd.CommandType = [Data.CommandType]::StoredProcedure

$p = $cmd.Parameters.Add('@output', '')
$p.Direction = [Data.ParameterDirection]::Output
$p.Size = 42

$cn.Open()
$cmd.ExecuteNonQuery()

$p.SqlValue.Value

[source]

Upvotes: 1

Related Questions