7

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.

My Stored Procedure:

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

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?

Thanks.

Improve this question
0

5 Answers 5

Reset to default
26

There are a several things you need to address to get it working

  1. The name is wrong its not @ouput its @code
  2. You need to set the parameter direction to Output.
  3. Don't use AddWithValue since its not supposed to have a value just you Add.
  4. Use ExecuteNonQuery if you're not returning rows

Try

SqlParameter output = new SqlParameter("@code", SqlDbType.Int);
output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(output);
cmd.ExecuteNonQuery();
MessageBox.Show(output.Value.ToString());
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

great, but how to get the value of the output parameter?? output.Value.ToString() giving error as "Object reference not set to an instance of an object."
and dont say "YOU FORGOT" because i did not forget, actually i dont know :-(
@rapsalands, after you execute the query, retrieve the value from the parameter. cmd.ExecuteNonQuery(); int value = (int)output.Value;
Not sure why you're getting a null reference. Perhaps you should update your question.
@rapsalands, your SP just looks plain wrong, nevermind your code for the moment. For one, I don't think you meant RecItemCode = @code, which is trying to assign the @code to RecItemCode. You never set @code.
|
9

The SQL in your SP is wrong. You probably want 

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

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

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

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.

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

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

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

Improve this answer

1 Comment

Thank you and +1. 'object obj = cmd.ExecuteScalar();' worked perfectly. I was able to accomplish what I needed in two lines of c# code, the second being 'myString = obj.ToString();'.
1

You need to define the output parameter as an output parameter in the code with the ParameterDirection.Output enumeration. There are numerous examples of this out there, but here's one on MSDN.

Improve this answer

Comments

1 
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;
Improve this answer

2 Comments

@rapsalands You can't just execute the code. It is not a standalone block of code. That error is saying that you haven't instantiated an object (i.e. create a new instance of it). It was just to give you the general idea of how to get a value from an output parameter of a stored proc.
Actually I am trying to learn SP and have never done it before. It will be great if you you can tell whats wrong with my code. I mean where i have to create the instance??
1

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.