Can you use cmd.ExecuteScalar when the sproc uses RETURN @value

c# ado.net executescalar
Blankman picture Blankman · Feb 6, 2009 · Viewed 8.1k times · Source

Can you use 

int blah = Convert.ToInt32(cmd.ExecuteScalar());

When the sproc's last statement does:

RETURN @value

I can only get it to work if it does:

SELECT @value

Also, this gives me a object null exception:

int blah = (int)cmd.ExecuteScalar();

isn't convert.toint32 and (int) the same thing but one is a wrapper of the other?

Answer

DCNYAM picture DCNYAM · Feb 6, 2009

No, you cannot. The ExecuteScalar() method is designed to return as single value that is returned in a result set. Basically, the value in the first column of the first row returned.

To get the return value, you need to add a parameter to your SQLCommand object. Use the name "@RETURN_VALUE" and specify a parameter direction of Return when creating the parameter object. You can then use the ExecuteNonQuery() method.

I must note that IMO, stored procedure return values should simply indicate the status of the procedure. All data should be returned through result sets or output parameters.

Related questions

Int32.TryParse() or (int?)command.ExecuteScalar()

I have a SQL query which returns only one field - an ID of type INT. And I have to use it as integer in C# code. Which way is faster and uses less memory? int id; if(Int32.TryParse(…

Read More
ExecuteScalar() returns null altough data was added to DB

I have a code as bellow where I try to insert a data into a table and return the ID (given by auto increment) of the new element. int newEquipmentID = new int(); query = database.ParameterizedQueryString("INSERT INTO Equipment (EquipmentTypeID) VALUES ({0})", "…

Read More
How to create a DataTable in C# and how to add rows?

How do create a DataTable in C#? I did like this: DataTable dt = new DataTable(); dt.clear(); dt.Columns.Add("Name"); dt.Columns.Add("Marks"); How do I see the structure of DataTable? Now I want to add ravi for …

Read More