How to use the OUTPUT parameter in a stored procedure

Question

I am new to writing a stored procedure. So I wrote one with the output parameters and I want to access the output value, it's hot to do it.

My saved procedure:

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

When trying to set "@code = RecItemCode" to get an error like: "The SELECT statement that assigns a value to a variable should not be combined with data extraction operations."

And I use a stored procedure like:

 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: "The reference to the object is not installed in the instance of the object." I want to get the value of the output parameter. How to get it?

Thanks.

Answer 1

SQL in your SP is incorrect. You probably want

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

In your application, you are not setting @code, you are trying to use it for the RecItemCode value. This will explain your NullReferenceException when trying to use the output parameter, because the value is never assigned to it, and you get the default value.

Another problem is that your SQL statement, if rewritten as

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

This is a mix of variable assignment and data extraction. This highlights a couple of points. If you need data that @code controls in addition to other pieces of data, forget the output parameter and just select the data.

 Select RecItemCode, RecUsername from Receipt where RecTransaction = @id 

If you just need code, use the first SQL statement that I showed you. For random cases, you really need output and data, use two different operators

 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, and also return two columns of data per row. However, this seems terribly redundant to me.

If you write your SP, as I showed at the very top, just call cmd.ExecuteNonQuery(); and then read the value of the output parameter.

---

Another issue with your SP and code. In your SP, you specified @code as varchar . In your code, you indicate the parameter type as Int . Either modify your SP, or your code to match types.

---

Also note: if everything you do returns one value, there is another way to do this that does not include output parameters at all. You can write

  Select RecItemCode from Receipt where RecTransaction = @id 

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

Answer 2

There are a few things you need to address in order to make them work.

• Incorrect name, not @ouput its @code
• You need to set the direction of the Output parameter.
• Do not use AddWithValue , since it should not matter only you Add .
• Use ExecuteNonQuery if you do not return rows

Try

 SqlParameter output = new SqlParameter("@code", SqlDbType.Int); output.Direction = ParameterDirection.Output; cmd.Parameters.Add(output); cmd.ExecuteNonQuery(); MessageBox.Show(output.Value.ToString()); 

Answer 3

You need to define the output parameter as the output parameter in the code listing ParameterDirection.Output . There are many examples of this, but here is one of the MSDN .

Answer 4

 SqlCommand yourCommand = new SqlCommand(); yourCommand.Connection = yourSqlConn; yourCommand.Parameters.Add("@yourParam"); yourCommand.Parameters["@yourParam"].Direction = ParameterDirection.Output; // execute your query successfully int yourResult = yourCommand.Parameters["@yourParam"].Value; 

Answer 5

You need to close the connection before you can use the output parameters. Something like that

 con.Close(); MessageBox.Show(cmd.Parameters["@code"].Value.ToString());