getting return on select

[Guest]

hey all,
i noticed when i run my stored procedure in sql studio i get a result set
from my select statement and then below that i get a return value. how about
when it's called from inside csharp. how do i get the return value then?

thanks,
rodchar

 

[Marc Gravell]

One option is to add a parameter with ParameterDirection.ReturnValue,
and then read the .Value after executing.

Marc

 

[Guest]

Most of the major commands in the SqlClient class such as ExecuteNonQuery,
return an integer value which indicates the results of ROWCOUNT (if it hasn't
been set to "OFF") that reflects the number of records affected by the SQL
Statement.

Here is a code example of how to get the RETURN VALUE from a sproc call:

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdatePrices";

SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;

SqlParameter paramPAF = new SqlParameter();
paramPAF.ParameterName = "@PriceMultiplier";
paramPAF.SqlDbType = SqlDbType.Decimal;
paramPAF.Direction = ParameterDirection.Input;
paramPAF.Value = textBoxPAF.Text;

SqlParameter paramNbrRows = new SqlParameter();
paramNbrRows.ParameterName = "@NbrRows";
paramNbrRows.SqlDbType = SqlDbType.Int;
paramNbrRows.Direction = ParameterDirection.Output;

cmd.Parameters.Add(paramReturnValue); // must be added first, parameter 0
cmd.Parameters.Add(paramPAF); // parameter 1
cmd.Parameters.Add(paramNbrRows); // parameter

After the stored procedure has been executed by calling the appropriate
execute method, the Parameters collection is used to retrieve data.
int returnValue = (int) cmd.Parameters["@return_value"].Value;
labelNbrPrices.Text = cmd.Parameters["@NbrRows"].Value.ToString() + "
prices updated";
Alternatively, ordinals could be used to retrieve values from the Parameters
collection.
int returnValue = (int) cmd.Parameters[0].Value; labelNbrPrices.Text =
cmd.Parameters[2].Value.ToString() + " prices updated";

Peter

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

hey all,
i noticed when i run my stored procedure in sql studio i get a result set
from my select statement and then below that i get a return value. how
about
when it's called from inside csharp. how do i get the return value then?

You add a parameter and set the parameter direction to ReturnValue:

SqlParameter myParameter = new SqlParameter("@Description", SqlDbType.Int);
myParameter.Direction = ParameterDirection.ReturnValue ;



NOTE:
The value of the parameter will be accesible ONLY AFTER you consume the
resultset from the query !!!!

 

[Bobbo]

NOTE:
The value of the parameter will be accesible ONLY AFTER you consume the
resultset from the query !!!!

Good catch, it took me ages to spot that little gem when I first
encountered it!

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Good catch, it took me ages to spot that little gem when I first
encountered it!

I think that we all had to deal with that painful discovery

 

[Guest]

thanks everyone for the help.

 

[Guest]

Good point Ignacio!
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net