• Blogs
• Of Outputs and Recordsets

Output Parameters AND Recordsets From a Stored Procedure

24 November 2006 - 10:32 PM / by Dominic Pettifer. 1 Image for Output Parameters AND Recordsets From a Stored Procedure. 9 Comments for Output Parameters AND Recordsets From a Stored Procedure.

Technical Article - Just had a frustrating past few hours with Stored Procedures in SQL Server 2005 and ASP.NET. I was trying to return both output parameters and a recordset from the same stored procedure and the caller (a .NET data access class) wasn’t having any of it! I found a solution so thought I better post it here to help others, and in case I forget.

Introduction

As you may know Stored Procedures allow you to encapsulate data access logic (SELECT, INSERT statements etc.) and have them executed on the database. All major databases support them including SQL Server, MySQL, Oracle etc.

Typically one would return a recordset of data with a SELECT statement based in some input parameters sent to the SProc (Stored Procedure). In other cases you may want to INSERT a new record and return just the primary key value that gets generated, you would use an OUTPUT parameter for this to return the ID to the caller.

But what if you wanted to return both a recordset and an output parameter (or parameters)? While this is perfectly possible, there are some caveats to look out for that could leave you pulling out your hair in frustration, almost like I did.

Problem

My problem was that I was trying to write a stored procedure that retrieved the currently active Poll, (see the Poll bit on the right), and the poll choices for that poll. They were stored in two tables, 'tblPolls' and 'tblPollChoices' with a one-to-many relationship. The current Poll's fields were returned as output params and the poll choices were returned as a recordset. This is the Sproc...

CREATE PROCEDURE db_Polls_GetCurrentPoll
(
    @PollID         INT           OUTPUT,
    @PollQuestion   VARCHAR(128)  OUTPUT,
    @DatePosted     DATETIME      OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT TOP 1 @PollID = plsID,
      @PollQuestion = plsQuestion,
      @DatePosted = plsDatePosted
    FROM tblPolls
    WHERE plsDateEnded IS NULL
    AND plsEnabled = 1
    ORDER BY plsDatePosted DESC
	
    SELECT pchID, pchChoiceText, pchVotes
    FROM tblPollChoices
    WHERE pchPollID = @PollID
    ORDER BY pchVotes DESC
END

With the above Sproc you would access it like this directly via Enterprise Manager or SQL Server Management Studio, and it worked perfectly. See screenshot below. So far so good I thought.

DECLARE @PollID         INT
DECLARE @PollQuestion   VARCHAR(128)
DECLARE @DatePosted     DATETIME

EXEC db_Polls_GetCurrentPoll
    @PollID OUTPUT,
    @PollQuestion OUTPUT,
    @DatePosted OUTPUT

SELECT @PollID AS 'Poll ID'
SELECT @PollQuestion AS 'Poll Question'
SELECT @DatePosted AS 'Date Posted'

The alternative was to use two stored procedures, or return two recordsets, but since the first recordset would only contain a single row it didn't seem appropriate.

Another reason you might return both output params and recordsets, is for a Stored Procedure that returns records in a table using a LIMIT or ROWCOUNT() clause (records 1 – 10, 11 – 20 etc.), and a count of all the records in a table (as an output param) for implementing custom pagination, something I did in the AsisSas project for custom paging of Seed Samples.

The database access code for the above Sproc was as follows. First I would set up the SqlCommand object...

SqlCommand cmd = new SqlCommand("db_Polls_GetCurrentPoll", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@PollID", SqlDbType.Int));
cmd.Parameters[“@PollID”].Direction = ParameterDirection.Output;

// Other output parameters here

Then I would first try to retrieve the output params before iterating through the recordset...

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    poll = new Poll();

    poll.Id = (int)cmd.Parameters[“@PollID”].Value;
    poll.Question = (string)cmd.Parameters[“@PollQuestion”].Value;
    poll.DatePosted = (DateTime)cmd.Parameters[“@DatePosted”].Value;

    while (reader.Read())
    {
        // Get poll choices code here
    }

    reader.Close();
}
else
{
    reader.Close();
}

The problem was that cmd.Parameters[“@ParamName”].Value; code was returning NULL, and the cast was throwing an ArgumentNullException (you can't have a NULL int and DataTime because they are value/primitive types). Basically the data wasn't there.

Solution

Then after much digging (Googling) I found that you must completely process the entire recordset BEFORE retrieving the output parameters otherwise, the output params will be empty. So changing the code to the following fixed the problem.

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    poll = new Poll();

    while (reader.Read())
    {
        // Get poll choices code here
    }

    reader.Close();

    // Get output params AFTER we've processed and CLOSED the SqlDataReadeer
    poll.Id = (int)cmd.Parameters[“@PollID”].Value;
    poll.Question = (string)cmd.Parameters[“@PollQuestion”].Value;
    poll.DatePosted = (DateTime)cmd.Parameters[“@DatePosted”].Value;
}
else
{
    reader.Close();
}

poll.Id, poll.Question and poll.DatePosted are now being correctly filled in, no more exceptions.

Conclusion

So to summarise, you can retrieve both output params and a recordset (or recordsets) as long as you do the following:

[ol]

• Make sure you retrieve the recordset first as an SqlDataReader via cmd.ExecuteReader()
• Process through all recordsets and then...
• Make sure you CLOSE the recordset, or SqlDataReader via reader.Close() before trying to retrieve the output parameters.

[/ol]

Notice in the code above reader.Close() is called before trying to retrieve the output params. That particular point had me stumped for ages, as I had originally called reader.Close() afterwards.

It seems the data is accessed sequentially and the client must consume all recordsets before any output or return values are sent to the client. Moral of the story: Google is your friend!

Have any questions, like to add something, or would like to correct me on something? Then please email me or post a comment for this blog. Thanks for reading!