1

What are the rules for when a stored procedure will return a result set?

If I add a SELECT statement near the bottom it gets returned as another result set.

But I add a SELECT to the beginning then it does not get returned in the result set.

Under what circumstances will a SELECT be returned as a result set? If it is followed by other statements it seems to not be returned, which ones?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cool breeze
  • 4,461
  • 5
  • 38
  • 67
  • 2
    Can you post an example where a SELECT statement does not get returned? – David Mar 28 '16 at 18:34
  • 1
    If you have a "normal" `SELECT` statement (not setting **variables** from the results - but just *selecting* the data) - it **will be** returned to the caller – marc_s Mar 28 '16 at 18:42
  • Are you saying you sometimes have more than one result set that gets returned? What does your stored procedure do after the `select` statement? What program are the results being handed back to? (This is important to know since they way you parse the results differs depending on the calling app.) – James L. Mar 28 '16 at 18:43
  • @marc_s yes that is one scenerio where the SELECT doesn't seem to be coming back in the result set i.e. when the variables are being set. – cool breeze Mar 28 '16 at 21:25

2 Answers2

2

A stored procedure can return multiple result sets.

If you are consuming the stored procedure from a front end application like asp.net, you may have to iterate through the result sets collection to see them all.

The only way the other statements in the stored procedure would prevent a SELECT statement from returning results would be if they prevent it from ever being executed. (Like IF...ELSE blocks, or the RETURN command which stops execution, etc.)

So to answer "what is the rule?", the rule is, if the SELECT statement gets executed at all, and if it returns a result set at all, then it will return a result set.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

Stored procedure returns the last SELECT as the result.

It does not include the select into or setting a variable using select

You can do some tricks to return kind of multiple result using UNION the results ad use an auxiliary field to segregate the results.

take a look into multiple result in stored procedure

Community
  • 1
  • 1
FLICKER
  • 6,439
  • 4
  • 45
  • 75