CODEWITHSUNDEEP
sql-server
cool breeze
cool breeze

Reputation: 4811

When will a stored procedure return a result set

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?

Upvotes: 1

Views: 81

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

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.

Upvotes: 2

FLICKER
FLICKER

Reputation: 6683

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

Upvotes: 0

Related Questions