0

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.

user1636171
  • 1
  • 1

2 Answers2

2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

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).

Sergei Rogovtcev
  • 5,804
  • 2
  • 22
  • 35