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.

Answer 1

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());