CODEWITHSUNDEEP
c#sql-serverstored-proceduressmomultiple-resultsets
user1636171
user1636171

Reputation: 1

Retrieving the number of results sets from a Sql server stored procedure from c#

Hi I am writing a T4 template to generate accessors for Sql server stored procedures. I have used a mixture of SMO and DeriveParameters method of CommandBuilder to get almost all of the information I need.

I am however unable to find a way of determining if and how many results sets a stored procedure returns.

In essence I am looking for a way to replicate Codesmiths SchemaExplorer CommandResults.Count property.

I don't wish to go down the road of parsing the test of the sproc.

Cheers in advance.

Upvotes: 0

Views: 349

Answers (2)

anon
anon

Reputation:

SQL Server does not store any metadata whatsoever about how many resultsets a stored procedure may return. There could be 0, 1, or n, and that could change depending on the logic in the stored procedure, parameter values you pass in, the state of the system, etc. - all things that can't be accounted for when retrieving metadata. Even the new 2012 metadata functionality only deals with describing the shape of the first resultset in a stored procedure - if there are more, you are on your own.

So no, I don't think there is any way to do this reliably. Even if the number of resultsets can't change depending on various factors, the best you're going to be able to do is the crappy SET FMTONLY ON; trick (which has its own set of problems) and count the results:

SET FMTONLY ON;
EXEC dbo.whatever;

This is obviously not good if executing the stored procedure changes the state of the system, but for reporting-style stored procedures it is probably okay in a lot of cases.

Upvotes: 2

Sergei Rogovtcev
Sergei Rogovtcev

Reputation: 5832

As far as I know there's no way to do that besides parsing stored procedure body (remember that it may contain conditionals and result count may be changing depending on them).

Upvotes: 0

Related Questions