TSQL - Parse Execution Plan to determine columns to be returned by a stored procedure

Question

Is there a way to dynamically determine (from .NET code or TSQL) the columns a stored procedure will return? I would like to dynamically generate wrapper functions in .NET for my stored procedures. It is easy to get proc names/parameters/etc. but I would also like to know what columns to expect when a procedure returns data (without executing that SP). Is this possible?

Answer 1

I don't think so.

Given a query execution plan you can determine what columns to get in result. But stored procedure usually combines a number of queries and can return more than one result set.

You can, however, 'do the job' in case you know that each of the procedures you're dealing with will return only one result set - so this will be you convention. In this case you can try to parse its' code, look for SELECT and determine a list of output columns.

Answer 2

This is actually notoriously tricky. It works fine with UDFs, since they have stronger metadata - but a stored procedure can do lots of nasty things:

• branches (IF etc), returning completely different shapes
• execute another stored procedure
• execute dynamic sql

So very, very tricky. There are two common approaches:

• attempt to parse the TSQL; painful
• execute it with default (null, etc) values and check the result

The SET FMTONLY ON option is often used for the second (to avoid updates etc), but note that system stored procedures are still executed (perhaps xp_sendmail?), so you risk doing unwanted things...

Answer 3

This is very similar to a prior question:

http://stackoverflow.com/questions/1028948/display-datatype-and-size-of-column-from-sql-server-query-results-at-runtime/1029095#1029095

Answer 4

I've seen it done, then I shouted at the client developer who did it and we moved away from the idea.

Seriously, we had problems with nested procs, adding columns or parameters (for later use etc), and other stuff because reflection second guessed our intentions.

However, there are MSDN articles on it:

• Auto-Generating Wrapper Classes for Stored Procedures Part 1, part 2
• Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET Metadata and Reflection

Edit: based on other answers, we don't normally change output based on IF statements. We treat view stored procs as methods (where sensible of course) so require a stable signature...

Answer 5

This is an uphill battle you're bound to loose. Just imagine a procedure like this:

create procedure usp_foo
     @p int
as
begin
if @p=1
  select col1 from table1;
if @p=2
  select col2 from table2;
if @p=3
  select col3 from table3;
end

Answer 6

A good case for returning results from a view instead of a sproc. (There are arguments both ways, but views have nice properties like closure and predictable structure.)