How to use OUTPUT parameter in Stored Procedure

Question

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

User · Answer

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

The name is wrong its not @ouput its @code
You need to set the parameter direction to Output.
Don't use AddWithValue since its not supposed to have a value just you Add.
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());

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[c#] How to use OUTPUT parameter in Stored Procedure

Examples related to c#

Examples related to sql-server-2008

Examples related to stored-procedures