存储过程中可以有可选的 OUTPUT 参数吗？

Question

我有一个存储过程，它有一堆输入和输出参数，因为它将值插入到多个表中。在某些情况下，存储过程仅插入到单个表中（取决于输入参数）。这是一个模拟场景来说明。

表/数据对象：

人员

Id
Name
Address

姓名

Id
FirstName
LastName

地址

Id
Country
City

假设我有一个插入人员的存储过程。如果该地址不存在，我不会将其添加到数据库中的 Address 表中。

因此，当我生成调用存储过程的代码时，我不想费心添加 Address 参数。对于 INPUT 参数来说这是可以的，因为 SQL Server 允许我提供默认值。但是对于 OUTPUT 参数，我在存储过程中做什么才能使其可选，这样我就不会收到错误......

过程或函数“Person_InsertPerson”需要参数 未提供“@AddressId”。

Answer 1

输入和输出参数都可以指定默认值。在这个例子中：

CREATE PROCEDURE MyTest
  @Data1 int
 ,@Data2 int = 0
 ,@Data3 int = null output

AS

PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)

SET @Data3 = @Data3 + 1

RETURN 0

第一个参数是必需的，第二个和第三个参数是可选的——如果调用例程没有设置，它们将被分配默认值。尝试使用不同的值和设置来摆弄它以及 SSMS 中的以下测试调用例程，看看它们是如何协同工作的。

DECLARE @Output int

SET @Output = 3

EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output

PRINT '---------'
PRINT @Output

Answer 2

输出参数和默认值不能很好地协同工作！这是来自 SQL 10.50.1617 (2008 R2)。 不要被愚弄，相信这个构造会神奇地代表您对该值执行SET（就像我的同事所做的那样）！

这个“玩具”SP 询问OUTPUT 参数值，无论它是默认值还是NULL。

CREATE PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
RETURN

如果您为 OUTPUT 发送未初始化的值（即 NULL），则 SP 内确实得到了 NULL，而不是 0。有道理，该参数传递了一些东西。

declare @QR int
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

输出是：

wtf its NULL
@QR=NULL

如果我们从调用者添加显式SET，我们会得到：

declare @QR int
set @QR = 999
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

并且（不足为奇）输出：

@QR=999

同样，有道理，传递了一个参数，并且 SP 没有对 SET 采取显式操作 一个值。

在 SP 中添加 OUTPUT 参数的 SET （就像您应该做的那样），但不要从调用者处设置任何内容：

ALTER PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
SET @QtyRetrieved = @Qty
RETURN

现在执行时：

declare @QR int
exec [dbo].[omgwtf] 1234, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

输出为：

wtf its NULL
@QR=1234

这是 SP 中OUTPUT 参数处理的“标准”行为。

现在来看看情节扭曲：获得默认值“激活”的唯一方法是根本不传递OUTPUT参数，恕我直言，这没有什么意义：因为它被设置为 OUTPUT 参数，这意味着返回应该收集的“重要”内容。

declare @QR int
exec [dbo].[omgwtf] 1
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

给出以下输出：

yay its zero
@QR=NULL

但这无法捕获 SP 输出，大概是该 SP 的目的。

恕我直言，这个功能组合是一个可疑的构造，我认为代码味道（唷！！）

Answer 3

看起来我可以向 OUTPUT 参数添加一个默认值，例如：

@AddressId int = -1 Output

由于 AddressId 严格用作 OUTPUT< /代码> 变量。但它有效。如果您有更好的解决方案，请告诉我。

Answer 4

补充一下 Philip 所说的内容：

我的 sql server 数据库中有一个存储过程，如下所示：

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) OUTPUT)

我从 .net 代码中调用它，如下所示：

 DataTable dt = SqlClient.ExecuteDataTable(<connectionString>, <storedProcedure>);

我收到一个 System.Data.SqlClient.SqlException：或函数需要参数“@current_phase”，但未提供该参数。

我还在程序的其他地方使用了这个函数，并传入一个参数并处理输出。这样我就不必修改当前的调用，我只是更改了存储过程以使输出参数也是可选的。

所以现在看起来如下：

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) = NULL OUTPUT)

Answer 5

由于您执行的是存储过程而不是 SQL 语句，因此必须将 SQL 命令的命令类型设置为存储过程：

cmd.CommandType = CommandType.StoredProcedure;

取自 此处。

另外，一旦消除了该错误，您就可以在过程中使用 SQL 的 nvl() 函数来指定遇到 NULL 值时要显示的内容。

抱歉没有正确解决这个问题......一定是误解了你。这是 nvl 的一个例子，我认为它可以更好地解决这个问题？

select NVL(supplier_city, 'n/a')
from suppliers;

如果 supplier_city 字段包含空值，上面的 SQL 语句将返回“n/a”。否则，它将返回 supplier_city 值。