Back to Basics – How to Retrieve a Value Returned by a SP
The Story Behind the Post
During this week one of my colleagues asked me if it’s possible get a
returned value of a stored procedure with the infrastructure I wrote to my
current project. The question regarded a returning value and not output
parameters which are another way to return a value from a stored procedure.
My answer was absolutely yes and I explained the process to him and to the
other members of the team who didn’t know how you can achieve that.
Stored Procedures and Returned Value
Every stored procedure returns a value even if you don’t use the
return keyword. That default returned value is an integer that holds
the value 0. The return value is limited to returning integer values only.
We can use the return functionality in order to return values to the calling
environment and notify some things like errors for example.
The Stored Procedure Example
In my example I use a School database. The stored procedure:
The stored procedure checks if exists in the database a course with
the given CourseID and returns 1 if true otherwise 0.
How to Retrieve a Value Returned by a Stored Procedure?
We can use simple ADO.NET functionality in order to get the returned value.
The only thing to do is to add a new parameter to the parameter list that we
send to the with our command. The parameter needs to have a Direction of
ParameterDirection.ReturnValue to indicate that its going to hold the return
value. Also, it’s DbType must be of type integer.
Lets look at an example of a simple way to call the stored procedure using
As you can see I create two parameters the first will hold the returned value.
After executing the command the returnedValue parameter will hold the
returned value from the stored procedure and I’m able to retrieve it and
use it in my code.
Lets sum up, in the post I showed how easy it is to retrieve a value
returned by a stored procedure. The example I showed is using a traditional
ADO.NET functionality. In my current project I use the enterprise library DAAB
in order to get the same functionality.