Stored procedure: return multiple columns instead of multiple recordsets?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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'