CODEWITHSUNDEEP
sqlstored-procedures
Brad Richards
Brad Richards

Reputation: 1253

Stored procedure: return multiple columns instead of multiple recordsets?

This is probably a really simple question, but I don't write stored procedures often and I'm a bit mystified...

After doing various stuff, the concluding bit of the SP ends by returning counts or sums from several different tables. The obvious approach is:

select SUM(ThisCol) as ThisResult from...
select SUM(ThatCol) as ThatResult from...
select count(DISTINCT OtherCol) as OtherResult from...

Of course, this creates multiple recordsets - one for each select plus one containing zero. This is a bit silly since each recordset contains exactly one value. I would much prefer to return a single recordset with multiple columns: ThisResult, ThatResult and OtherResult.

Is that possible?

Upvotes: 0

Views: 3514

Answers (3)

Kane
Kane

Reputation: 16802

You can use variables

DECLARE @thisResult INT
DECLARE @thatResult INT
DECLARE @otherResult INT

select @thisResult = SUM(ThisCol) as ThisResult from...
select @thatResult = SUM(ThatCol) as ThatResult from...
select  @otherResult = count(OtherCol) as OtherResult from...

SELECT  @thisResult AS 'thisResult', @thatResult AS 'thatResult', @otherResult AS 'otherResult'

Upvotes: 2

David Andres
David Andres

Reputation: 31781

If you're using SQL Server, you can select these quantities again as your last statement.

Select ThisResult, ThatResult, OtherResult

You don't have to specify a table

Upvotes: 0

manji
manji

Reputation: 47978

SELECT T1.ThisResult, T2.ThatResult, T3.OtherResult
  FROM (select SUM(ThisCol) as ThisResult from...) T1,
       (select SUM(ThatCol) as ThatResult from...) T2,
       (select count(DISTINCT OtherCol) as OtherResult from...) T3

Because each table contains only 1column & 1 value, you do a cross join of all 3 and put each value in a column in the result table.

Upvotes: 1

Related Questions