SQL Server存储过程中未设置输出变量

本文关键字：设置 输出 变量 过程中 Server 存储 存储过程 SQL | 更新日期: 2023-09-27 18:27:01

我希望这不是我事后问自己的问题之一，但这真的让我很困惑。两者的设置基本相同。以下是正在发生的事情。

下面是我的存储过程示例：

ALTER PROCEDURE [dbo].[CreateRecord] 
    -- Add the parameters for the stored procedure here
    @Link1Id INT = NULL,
    @Link2Id INT = NULL,
    @Amount MONEY,
    @Output int out
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET @Output = 0
    -- Insert statements for procedure here
    IF @Link1Id = NULL
    BEGIN
        IF NOT EXISTS(SELECT * FROM dbo.Records WHERE Link2Id = @Link2Id)
        INSERT INTO [dbo].[Records]
               ([Link1Id]
               ,[Link2Id])
         VALUES
               (@Link1Id
               ,@Link2Id)
        SET @Output = (SELECT RecordId FROM dbo.Records WHERE Link2Id = @Link2Id)
    END
    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM dbo.Records WHERE Link1Id = @Link1Id)
        INSERT INTO [dbo].[Records]
               ([Link1Id]
               ,[Link2Id])
         VALUES
               (@Link1Id
               ,@Link2Id)
        SET @Output = (SELECT RecordId FROM dbo.Records WHERE Link1Id = @Link1Id)
    END
END

现在，我创建了一个单元测试，基本上运行这个过程，并尝试Assert返回的@Output大于0，但@Output参数在代码中的SqlCommand上从未有值。以下是一些C#代码：

private int ExecuteNonQueryWithOutput(string procedureName, SqlParameter[] parameters)
{
    SqlCommand command = this.GenerateCommand(procedureName, parameters);
    connection.Open();
    command.ExecuteNonQuery();
    int retval = (int)command.Parameters[OUTPUT].Value;
    connection.Close();
    return retval;
}

现在，我可以跨过调用ExecuteNonQuery()的行，在数据库中验证新的（正确的）记录是否存在，但在下一行，它在调用(int)command.Parameters[OUTPUT].Value;时抛出异常，因为Value不存在。

这对于我的另一个程序来说是完美的，它是以同样的方式设置的。你知道为什么它在这里不起作用吗？

谢谢，我有点不知所措。我调试了一段时间，但运气不好。

编辑：

生成参数数组的代码：

List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter { ParameterName = "@Link1Id", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = link1Val });
parameters.Add(new SqlParameter { ParameterName = "@Link2Id", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = link2Val });
parameters.Add(new SqlParameter { ParameterName = OUTPUT, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output });
return this.ExecuteNonQueryWithOutput("CreateRecord", parameters.ToArray());

SQL Server存储过程中未设置输出变量

我不知道您在哪里声明了@Output。你的意思是：

ALTER PROCEDURE [dbo].[CreateRecord] 
    -- Add the parameters for the stored procedure here
    @Link1Id INT = NULL,
    @Link2Id INT = NULL,
    @Amount MONEY,
    @Output INT = NULL OUTPUT
AS

此外，我不能100%确定您是否具有检索命名输出参数的语法权限。但是参数必须存在，然后才能引用它。如何在不声明@Output的情况下保存该存储过程？

除了输出参数问题之外，代码还有很多错误。

要回答实际问题，您可能会将NULL值作为Output返回。当它试图将其转换为Int时，你会得到一个错误

还有sql行：

IF @Link1ID = null

将始终失败。用SQL的话说，null是一个不确定的值，所以（null！=null）。测试空值的方法是使用IS。例如：

IF (@Link1ID is null)

这让我相信，您实际上在sql代码中遇到了主键冲突。

现在，谈谈更大的问题。你的C#代码有缺陷。命令对象永远不会被处理，如果有任何问题，您的连接对象也不会被处理。由于可用的sql连接用完，这将导致有趣的sql错误。。

它应该看起来像以下内容：

private int ExecuteNonQueryWithOutput(string procedureName, SqlParameter[] parameters)
{
    int retval = 0;
    using (SqlConnection conn = new SqlConnection("connection string here"))
    using (SqlCommand command = this.GenerateCommand(procedureName, parameters)) {
        connection.Open();
        command.ExecuteNonQuery();
        retval = (int)command.Parameters[OUTPUT].Value;
    }
    return retval;
}

请注意，这会在本地声明、使用和处置连接和命令对象。如果出现问题，这将确保资源得到妥善处理。

还要注意，它不使用全局"连接"对象。操作系统提供的连接池在根据需要打开/关闭连接时非常高效。正因为如此，最佳实践是实例化并保持它们的存在时间仅足以处理当前操作。它打开的时间越长，你遇到问题的可能性就越大。

相关文章：