如何获取存储过程OUTPUT变量的值 [英] How to get the value of a stored procedure OUTPUT variable

问题描述

我有一个简单的 SQL Server 存储过程，它接受一个输入参数并设置一个输出参数.存储过程的本质如下:

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

我想在 PowerShell 中获取 @output 变量的值.如果我要在 SSMS 中执行此操作，我会执行以下操作:

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;

但是，我认为在 PowerShell 中没有那么简单.

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

这是我目前在 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"
}

如何在 PowerShell 中获取 @output 变量的值?

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

推荐答案

这样的事情可能会奏效:

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

[来源]

这篇关于如何获取存储过程OUTPUT变量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！