How many ways to Returning Data through Staored Procedures

Stored Procedures: Returning Data

---

This article discusses three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.  Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results. (This article has been updated through SQL Server 2005.)

All the examples in this article use the AdventureWorks database and have been tested through SQL Server 2008. All the client code examples are written using ASP.NET 2.0.

 

Result Sets

Result sets are what you get when you run a simple SELECT statement inside a stored procedure. Let's suppose you want a stored procedure to return a list of all the people with a given last name.  The code for the stored procedure might look like this:

CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
    FirstName,
    LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID

If you just execute this stored procedure in SQL Server Management Studio you get a result set that looks like this:

EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

  ContactID FirstName                           LastName
----------- ----------------------------------- -----------------------
         22 J. Phillip                          Alexander
         23 Michelle                            Alexander
        430 Mary                                Alexander
        
 . . . { hiding a bunch of rows } . . .
 
      19942 Morgan                              Alexander

(123 row(s) affected)

If you want to write a web page that calls this stored procedure and processes the results that code you'll need to add a using clause for the SqlClient namespace.  This is needed for all the client side samples.

using System.Data.SqlClient;

The code itself might look like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetPeopleByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
        conn.Open();
        cmd.Connection = conn;
      
        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                int contactID = rdr.GetInt32(rdr.GetOrdinal("ContactID"));
                string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
                Response.Write(firstName + " (" + contactID.ToString() + ")");
            }
            rdr.Close();
        }
    }
}

If you want to capture this result set using T-SQL you'll need a place to store it.  Temporary Tables work well for that.  That code might look something like this:

DECLARE  @People TABLE (
    ContactID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

(123 row(s) affected)
           
-----------
        123

(1 row(s) affected)

This code let's us capture the result set from the stored procedure into a table variable or temporary table.  I use code like this when I work with packaged applications.  I call their stored procedures to get the results I want and then manipulate it as needed.

 

Using OUTPUT variables

If you just want to return a single value (or a couple of values) you can use output variables.  The stored procedure looks like this:

CREATE PROCEDURE dbo.GetCountByLastName (
    @LastName NVARCHAR(50),
    @LastNameCount INT OUTPUT )
AS
SELECT @LastNameCount = COUNT(*)
FROM Person.Contact
WHERE LastName = @LastName

If we want to return the value using T-SQL we'd use code like this:

DECLARE @TheCount INT

EXEC dbo.GetCountByLastName 
    @LastName = 'Alexander',
    @LastNameCount = @TheCount OUTPUT

SELECT TheCount = @TheCount
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

   TheCount
-----------
        123

(1 row(s) affected)

And we can see that there are still 123 Alexanders in our database.  To call run this stored procedure from ASP.NET we'd need code that looked like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetCountByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));

        SqlParameter countParameter = new SqlParameter("@LastNameCount", 0);
        countParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(countParameter);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
        Response.Write("<p>Count: " + count.ToString());
        conn.Close();
    }
}

 

Using Return

The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. Consider this procedure:

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue
GO

All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure:

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturn 3
SELECT ReturnValue=@ReturnValue

and the output looks like this:

ReturnValue 
----------- 
3

(1 row(s) affected)

Whatever number is returned using the RETURN statement is put into the variable @ReturnValue.  The ASP.NET code to get the return value looks like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.TestReturn"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Invalue", 3));

        SqlParameter returnValue = new SqlParameter("@Return_Value", DbType.Int32);
        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());
        Response.Write("<p>Return Code: " + count.ToString());
        conn.Close();
    }
}

Those are the three best ways I know of to get data back from a stored procedure. Enjoy!

Post  By Bill Graziano