How to – Get Return Value From Stored Procedure

Up to now I was all about problem solving. I decided to, from now on, post “how to” posts too.

First one is about returning a scalar value from a stored procedure and getting that value in the code.

There are two ways you can return (get) a scalar value from a stored procedure.

1- You can use “return” statement
2- You can use “select” statement

These 2 different ways require 2 different approaches to get the values from the executed procedure.

Case: I want to check a condition in the database using a stored procedure, and return a scalar value indicating the condition. Conditions may be existence/ non-existence of a record, min/max number of records have been met or not etc.

WAY-1 using “return” statement in the procedure to return the scalar

Sample Stored Procedure:

--set the variables
@var1 nvarchar(10),
@count int

as
–execute condition  check
@count=(select count(1) from “myTable” where “myColumn[email protected])
if (@count>”maxValue”)
return 1
else
return 0

When you are using the “return” statement you need to add your command a new parameter for the returned value. You will need to do the following “3” in your code.

The below code assumes creation of SqlConnection object, SqlCommand object etc. are known to the reader and he understands that the code is still to be completed, yet it gives the idea…
1-Add the parameter for your return value


SqlParameter myReturn = new SqlParameter("returnValue", SqlDbType.Int);
myReturn.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myReturn);

2-Open connection, Execute the command

myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

3-Return the value

return Convert.ToInt32(myReturn.Value);

As you can see here, you will need the Value of “myReturn”, you will not have direct access to the returned value using “myCommand”.

WAY-2 using “select” statement in the procedure to make it directly accessible

Sample Stored Procedure:


--set the variables
@var1 nvarchar(10),
@count int

as
–execute condition  check
@count=(select count(1) from “myTable” where “myColumn[email protected])
if (@count>”maxValue”)
select 1
else
select 0

This time you won't need steps 1 and 3 above. All you need to do is execute your command (myCommand) but this time as ExecuteScalar!

int what_is_returned = (int)myCommand.ExecuteScalar();

Because this time you will have direct access to the resulting scalar value of the condition in your Stored Procedure.

Hope this helps someone.

Good luck,
Serdar

Leave a Reply

Your email address will not be published. Required fields are marked *