How to return two result sets from stored procedure?

Question

I have two queries in the current page. They are very similar but still returning different number of records. Here is example:

Query 1

SELECT 
   recid,
   cost,
   cur_year
FROM Info i
   INNER JOIN Extension ex
       ON ex.recid = i.recid
       AND ex.categoryid = i.categoryid
WHERE cur_year = 2018
    AND info_id = 4531

Query 2

SELECT SUM(cost) AS cost_total
FROM Info i
   INNER JOIN Extension ex
       ON ex.recid = i.recid
       AND ex.categoryid = i.categoryid
WHERE cur_year = 2018
    AND info_id = 4531
    AND cost > 0

Query 1 in this case returns 2 records. Query 2 returns 1 record. I'm wondering if putting these two in SP would be a good idea? Current code has two separate queries and automatically hitting the database twice. My idea is two have one call to database and return two data sets. If anyone have example how this can be achieved please let me know. Thank you.

it should be fine if your client can read both the resultsets — PSK, Feb 28 '19 at 12:21
@a_horse_with_no_name I just updated my question. The sybase database. — espresso_coffee, Feb 28 '19 at 12:21
@TabAlleman For some reason someone decided to edit the original question. He/She added `tsql`... — espresso_coffee, Feb 28 '19 at 14:11

score 0 · Answer 1 · answered Feb 28 '19 at 12:22

0

Why not just include another column? Two queries in a stored procedure is still hitting the database twice.

And, you can express this as:

SELECT recid, cost, cur_date,
       SUM(CASE WHEN cost > 0 THEN cost ELSE 0 END) OVER () as total_cost
FROM Info i INNER JOIN
     Extension ex
     ON ex.recid = i.recid AND
        ex.categoryid = i.categoryid
WHERE cur_date = '2018' AND  -- this is suspicious.  Dates are not years
      info_id = '4531';

answered Feb 28 '19 at 12:22

Gordon Linoff

1,242,037
58
646
786

I'm getting more than 2 records if I apply your code above. Looks like `sum()` function takes more records than it should. – espresso_coffee Feb 28 '19 at 12:24
@espresso_coffee . . . You should be getting the same rows as in your first query. – Gordon Linoff Feb 28 '19 at 13:37
I'm getting way more rows than in the original query plus error message near `OVER()` if I do not remove that. – espresso_coffee Feb 28 '19 at 13:40

score 0 · Answer 2 · answered Feb 28 '19 at 18:49

0

As some callers initiate return at first result set delivered, set nocount on at the beginning of your proc body. I know of no ways to have one query return 2 sets.

set nocount on;

answered Feb 28 '19 at 18:49

JBJ

393
3
8

How to return two result sets from stored procedure?

2 Answers2