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