如何使用OUTPUT参数的存储过程 [英] How to use OUTPUT parameter in Stored Procedure

问题描述

我是新来编写存储过程。所以我写了一个用输出参数和要访问的产值，热做到这一点。

I am new to writing Stored Procedure. So I wrote one with output parameters and want to access the output value, hot to do it.

我的存储过程：

ALTER PROCEDURE selQuery
    (
        @id int, @code varchar(50) OUTPUT
    )
AS
    SELECT RecItemCode = @code, RecUsername from Receipt where RecTransaction = @id
    RETURN @code

如果试图设置@码= RecItemCode收到错误为：那赋值给变量不能与数据检索操作结合使用SELECT语句

If trying to set "@code=RecItemCode" getting error as : "A SELECT statement that assigns a value to a variable must not be combined with Data Retrieval operations."

而我使用的存储过程为：

And I am using the Stored Procedure as:

con.Open();
cmd.Parameters.AddWithValue("@id", textBox1.Text);
SqlParameter code = new SqlParameter("@code", SqlDbType.Int);
code.Direction = ParameterDirection.Output;
cmd.Parameters.Add(code);
SqlDataReader sdr = cmd.ExecuteReader();
MessageBox.Show(cmd.Parameters["@code"].Value.ToString()); // getting error
con.Close();

错误：对象引用不设置到对象的实例。
我想获得输出参数的值。如何获得的？

Error : "Object reference not set to an instance of an object." I want to get the value of output parameter. How to get that?

感谢。

推荐答案

在SQL中你的SP是错误的。你可能想

The SQL in your SP is wrong. You probably want

Select @code = RecItemCode from Receipt where RecTransaction = @id

在你的说法，你是不是设置@code，你要使用它RecItemCode的价值。这可以解释你的的NullReferenceException 当您尝试使用输出参数，因为值永远不会分配给它，你得到一个默认为空。

In your statement, you are not setting @code, you are trying to use it for the value of RecItemCode. This would explain your NullReferenceException when you try to use the output parameter, because a value is never assigned to it and you're getting a default null.

另一个问题是，如果改写为

The other issue is that your SQL statement if rewritten as

Select @code = RecItemCode, RecUsername from Receipt where RecTransaction = @id

这是混合变量赋值和数据恢复。这突出了几个百分点。如果你需要的是推动@code除了数据的其他部分的数据，忘记了输出参数，只是选择数据。

It is mixing variable assignment and data retrieval. This highlights a couple of points. If you need the data that is driving @code in addition to other parts of the data, forget the output parameter and just select the data.

Select RecItemCode, RecUsername from Receipt where RecTransaction = @id

如果你只需要代码，使用第一个SQL语句，我向您展示。在副手的机会，你的实际需要输出的和的数据，使用两个不同的语句

If you just need the code, use the first SQL statement I showed you. On the offhand chance you actually need the output and the data, use two different statements

Select @code = RecItemCode from Receipt where RecTransaction = @id
Select RecItemCode, RecUsername from Receipt where RecTransaction = @id

这应该指定你的价值的输出参数以及返回连续两列数据。然而，这令我非常多余。

This should assign your value to the output parameter as well as return two columns of data in a row. However, this strikes me as terribly redundant.

如果你写你的SP，因为我在最高层已经证明，简单地调用 cmd.ExecuteNonQuery（ ）; ，然后读取输出参数值

If you write your SP as I have shown at the very top, simply invoke cmd.ExecuteNonQuery(); and then read the output parameter value.

与SP和代码的另一个问题。在你的SP，您已声明@code为 VARCHAR 。在您的代码，您指定的参数类型为内部。要么改变你的SP或您的代码，以使类型相一致。

Another issue with your SP and code. In your SP, you have declared @code as varchar. In your code, you specify the parameter type as Int. Either change your SP or your code to make the types consistent.

另外请注意：如果你正在做的是返回单个值，还有另一种方式来做到这一点，它不是在所有涉及输出参数。你可以写

Also note: If all you are doing is returning a single value, there's another way to do it that does not involve output parameters at all. You could write

 Select RecItemCode from Receipt where RecTransaction = @id

然后用 obj对象= cmd.ExecuteScalar（）; 来得到结果，不需输出参数在SP或在你的代码。

And then use object obj = cmd.ExecuteScalar(); to get the result, no need for an output parameter in the SP or in your code.

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