Determining whether a Stored Procedure returns a resultset

Question

Determining whether a Stored Procedure returns a resultset

PhilPacha

Hall of Fame

Points: 3645
More actions
January 28, 2009 at 12:12 pm

#129841

A former co-worker has asked this question; I admit, I'm a bit intrigued.
Given the name of a stored procedure, is there an “efficient” way to determine that the stored procedure returns a result set (without actually executing the procedure)?
If there's a way before SQL Server 2005, I'd like to know that, too.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Scott Hembrough Newbie Points: 9 More actions · Answer 1

I'm actually the former co-worker... 😛

To expand on this, we're writing a VB.NET code generating utility, and it would be very helpful to know if a given stored procedure generates a result set. We were hoping we could check a system table/view or call a system function to determine this. Obtaining this information via SMO would be fine as well. Any suggestions would be greatly appreciated. Thanks!

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 2

I don't believe there's an easy way to do that, all though I could certainly be wrong. The fly in the ointment is what happens if a sproc returns more than 1 result set? See the dilema?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 3

SET FMTONLY ON

http://technet.microsoft.com/en-us/library/ms173839.aspx

Ran across this one as one of our client side apps was running it on its own and it drove me NUTS until I managed to catch it in profiler and figured out what the heck it was doing. I then had to embed the SET FMTONLY tags with my own inside my codeline so that after it turned it on, I turned it back off and I could use the data from the query.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

PhilPacha Hall of Fame Points: 3645 More actions · Answer 4

Jeff, does that mean you're aware of a 'non-easy' method for determining whether a Stored Procedure returns a resultset? And, no, I don't see any dilema; we're interested in whether it returns *any* resultset, not the number of resultsets returned.

Garadin, what does SET FMTONLY ON have to do with determining programatically whether a stored procedure returns a resultset?

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 5

PhilPacha (1/29/2009)
Jeff, does that mean you're aware of a 'non-easy' method for determining whether a Stored Procedure returns a resultset? And, no, I don't see any dilema; we're interested in whether it returns *any* resultset, not the number of resultsets returned.
Garadin, what does SET FMTONLY ON have to do with determining programatically whether a stored procedure returns a resultset?

SET FMTONLY ON

GO

EXEC sp_spaceused 'sometablename'

GO

SET FMTONLY OFF

... returns nothing. Even if it did, you'd still have to satisfy all the input parameters so not sure that would actually help.

The *any* way to do it, would be to programatically examine the contents of sys.SysComment.Text. I'm thinking that some form of RegEx (through a CLR) would make your life a bit easier. Understand that I've not done this myself... it's just a "possibility". I couldn't write a CLR in C# if my life depended on it.

You might be able to do such a thing in T-SQL with several PATINDEX's, but I'd really have to think about what to check for between a SELECT and a FROM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Oops, sorry for the double post, my internet freaked out last night. To be honest, I'm not sure how you'd use FMTONLY to do it, but I do think it can be done. I think that's what LINQ to SQL uses to do it.

Now precisely how it uses the information returned by FMTONLY to figure out all the info about the return set? Can't figure that out. I believe our primary ERP system uses it for the same purpose though.

I was kinda hoping that after throwing it out there, someone with a lot more experience with this sorta thing might be able to jump in with how to work with it :blush:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Jeff Moden (1/28/2009)
I don't believe there's an easy way to do that, all though I could certainly be wrong. The fly in the ointment is what happens if a sproc returns more than 1 result set? See the dilema?

Or even worse, one that returns different results sets or that may not return any result set based on conditions both implicit and explicit.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

In short, not only is this not reasonably possible, it is theoretically impossible.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

krayknot SSCertifiable Points: 7998 More actions · Answer 9

PhilPacha (1/28/2009)
A former co-worker has asked this question; I admit, I'm a bit intrigued.
Given the name of a stored procedure, is there an “efficient” way to determine that the stored procedure returns a result set (without actually executing the procedure)?
If there's a way before SQL Server 2005, I'd like to know that, too.

If the result in the parameter user

sp_help

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 10

krayknot (1/29/2009)
PhilPacha (1/28/2009)
A former co-worker has asked this question; I admit, I'm a bit intrigued.
Given the name of a stored procedure, is there an “efficient” way to determine that the stored procedure returns a result set (without actually executing the procedure)?
If there's a way before SQL Server 2005, I'd like to know that, too.
If the result in the parameter user
sp_help

Good idea, but they're not looking for parameters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Hembrough Newbie Points: 9 More actions · Answer 11

The *any* way to do it, would be to programatically examine the contents of sys.SysComment.Text. I'm thinking that some form of RegEx (through a CLR) would make your life a bit easier. Understand that I've not done this myself... it's just a "possibility". I couldn't write a CLR in C# if my life depended on it.

I'm thinking the CLR approach would be the best way as well, and will probably go this direction. Thanks for the suggestion.

Scott Hembrough Newbie Points: 9 More actions · Answer 12

RBarryYoung (1/29/2009)
Or even worse, one that returns different results sets or that may not return any result set based on conditions both implicit and explicit.

Valid point. For our purposes, we don't have to be absolutely precise. What we're doing (without getting into a bunch of gory details) is writing a VB.NET code generator that will generate the appropriate ADO.NET code for a stored procedure. To do this effectively, we need to know if a stored proc will return a result set (and provide an option for the developer to override this, if the utility is unable to figure this out). If we can be 90% accurate on this, I think it would be acceptable.

As mentioned above, the code can be parsed and evaluated. We'll probably go this direction to ensure as much accuracy as possible. We were just looking for a shortcut... 😛

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

The only way close to 90% is to parse the SQL of the procedure and see if that are any "data retrieval" SELECT statements (not sub-queries). Thats no easy job itself, as accurately parsing SQL is a bear of a job. I believe that is is an LALR(N) grammar and they just don't write language specs like that anymore (attempting to change this is in fact the primary reason why they are trying to force us to use semicolons).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]