Back to Basics – How to Retrieve a Value Returned by a SP

October 31, 2008

Back to Basics – How to Retrieve a Value Returned by a SP

In the following posts How to Retrieve a Value Returned by a SP
I’m going to describe
basic tools which every
developer needs to
know. In today’s post
I’m going to explain
how to retrieve a value
returned by a stored procedure
.

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:

CREATE PROCEDURE SP_IsCourseExists

    @CourseID int

AS

BEGIN

    SET NOCOUNT ON;

 

    IF EXISTS (SELECT CourseID FROM Course WHERE CourseID = @CourseID)

       RETURN 1

    ELSE

       RETURN

END

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
ADO.NET:

private string IsCourseExists(int courseID)

{

   using (SqlConnection conn =

      new SqlConnection(

         ConfigurationManager.ConnectionStrings["School"].ConnectionString))

   {

      // create the command

      SqlCommand cmd = new SqlCommand("SP_IsCourseExists", conn);

      cmd.CommandType = CommandType.StoredProcedure;

 

      // create the returned value parameter

      SqlParameter returnedValue = cmd.CreateParameter();

      returnedValue.Direction = ParameterDirection.ReturnValue;

      returnedValue.SqlDbType = SqlDbType.Int;

      returnedValue.ParameterName = "@ReturnValue";

 

      // create the course id input parameter

      SqlParameter courseIDParam = cmd.CreateParameter();

      courseIDParam.ParameterName = "@CourseID";

      courseIDParam.Value = courseID;

 

      cmd.Parameters.Add(returnedValue);

      cmd.Parameters.Add(courseIDParam);

 

      // execute the command

      conn.Open();

      cmd.ExecuteNonQuery();

      string output = (int)returnedValue.Value == 0 ?

         "Not exists" : "Exists";

      conn.Close();

      return output;

   }

}

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.

Summary

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.  

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>