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.

Sandy
  • 11,332
  • 27
  • 76
  • 122

5 Answers5

25

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());
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • 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." – Sandy Oct 14 '11 at 17:02
  • and dont say "YOU FORGOT" because i did not forget, actually i dont know :-( – Sandy Oct 14 '11 at 17:02
  • @rapsalands, *after* you execute the query, retrieve the value from the parameter. `cmd.ExecuteNonQuery(); int value = (int)output.Value;` – Anthony Pegram Oct 14 '11 at 17:09
  • Not sure why you're getting a null reference. Perhaps you should update your question. – Conrad Frix Oct 14 '11 at 17:10
  • @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. – Anthony Pegram Oct 14 '11 at 17:17
  • @Antony: but when i write "@code=RecItemCode" it gives error "A SELECT statement that assigns a value to a variable must not be combined with Data Retrieval operations" – Sandy Oct 14 '11 at 17:23
  • @rapsalands, OK. However, leaving it the way you have it is not correct. Let me ask, what do you actually want from this stored procedure? The data from the table or the output value in the parameter? From your query, it doesn't look like you really need *both*, as the output would already be in the data. And if you just need the output, you are selecting *too much* in the query. – Anthony Pegram Oct 14 '11 at 17:30
  • @Antony: I was just trying to learn SP and output parameter. An here, I want to get the RecItemCode value in the output parameter and access it in my code – Sandy Oct 14 '11 at 17:34
  • @rapsalands, you can correct your issue by dropping the output parameter *or* dropping `, RecUsername` from the SQL (and fixing the parameter assignment). *Or* you can fix your issue by using two separate SQL statements: one to set the output parameter, one to retrieve your data. – Anthony Pegram Oct 14 '11 at 17:34
  • it will really great if you can post a sample as a different answer. Please this SP is haunting my sweet dreams – Sandy Oct 14 '11 at 17:36
  • @rapsalands, ok, I've expanded my thoughts into an answer, hopefully it will make more sense laid out that way. – Anthony Pegram Oct 14 '11 at 17:47
8

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.

Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246
  • 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();'. – FumblesWithCode May 23 '14 at 17:51
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.

Bryan Crosby
  • 6,486
  • 3
  • 36
  • 55
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;
  • @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. –  Oct 14 '11 at 17:38
  • 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?? – Sandy Oct 14 '11 at 17:45
0

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());
HebeleHododo
  • 3,620
  • 1
  • 29
  • 38
Sean
  • 9
  • 1