SQL Stored Procedure OUTPUT Return Multiple Rows

Question

I need to return a list of all reports in a report table that have not been processed yet. I am using stored procedures for this process.

Here is my stored procedure creation:

CREATE PROCEDURE spGetAllUntouchedReportID
@ReportID int OUTPUT

AS
BEGIN
    SELECT @ReportID=Report.ReportID
    FROM Report
    WHERE Report.Status IS NULL
END

And this is how I am calling the stored procedure:

DECLARE @ReportID int
EXECUTE spGetNextReportID
@ReportID OUTPUT
Print @ReportID

Essentially a C# app will be connecting and getting the result set but I am using the call to test it out. My problem is that the call to the stored procedure is only printing the LAST result it found.

Here is an example set for the table of reports:

ReportID  Status
1         NULL
2         Incomplete
3         NULL

A call to the procedure prints out:

3

Instead of:

1
3

How do I get my stored procedure to store the set that it returns from the stored procedure and call it to print each one out? This may come up in other stored procedures too where I need to do a Select * that has multiple columns and need to return the entire set

Answer 1

The OUTPUT can hold one value in it. If you want to return a list of values, then you should alter the procedure to the following:

CREATE PROCEDURE spGetAllUntouchedReportID

AS
BEGIN
    SELECT Report.ReportID
    FROM Report
    WHERE Report.Status IS NULL
END

This will return all values that have a Status of null.

See SQL Fiddle with Demo

Answer 2

My suggestion is to turn your stored procedure into a function:

CREATE function spGetAllUntouchedReportID
@ReportID int OUTPUT
returns table as
    return (SELECT @ReportID=Report.ReportID
            FROM Report
            WHERE Report.Status IS NULL
           )

The subject of returning and sharing values between stored procedures offers many solutions. Erland Sommerskog has an excellent blog article on this subject.