Stored Procedures: Returning Data

By Bill Graziano on 9 April 2001 | Tags: Stored Procedures


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!


Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

Performance tuning (4h)

As I gain experience and get older, I'm working much slower, but producing better quality, but (7h)

Master DB 2019 problem (22h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

- Advertisement -