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:

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

Is there any reason why you are returning the result using an output variable? Why not just use a select statement like: SELECT ReportID FROM Report WHERE Status IS NULL? — Ɖiamond ǤeezeƦ
– Ɖiamond ǤeezeƦ, Commented Apr 21, 2013 at 0:34
@ƉiamondǤeezeƦ Im new to SQL Stored Procs and from the examples I saw they used OUTPUT to store results, but apparently that was meant for only a single value. — Kairan
– Kairan, Commented Apr 21, 2013 at 0:38
sqlzoo.net has interactive SQL tutorials that you may find helpful to learn about stored procedures and SQL in general. — Ɖiamond ǤeezeƦ
– Ɖiamond ǤeezeƦ, Commented Apr 21, 2013 at 0:42

Taryn · Accepted Answer · 2013-04-21 00:34:33Z

17

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

answered Apr 21, 2013 at 0:34

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kairan Over a year ago

That is useful information and a great tool I did not know about, thanks!

Gordon Linoff · Accepted Answer · 2013-04-21 00:43:04Z

13

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.

answered Apr 21, 2013 at 0:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Stored Procedure OUTPUT Return Multiple Rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related