Stored Proc Result Set Columns

Question

Stored Proc Result Set Columns

Dave Mason

SSCrazy

Points: 2751
More actions
November 25, 2009 at 10:10 am

#96487

I can use various system views to determine the names and data types of the columns in tables and/or views (ie INFORMATION_SCHEMA.COLUMNS, sys.columns, etc).
Is there a way to determine the column names/data types of a result set returned by a stored procedure?
I'd like to do this programatically, with the caveat that there is no access to the tsql code of the stored proc.
Dave MasonSeminole County, FL

Viewing 4 posts - 1 through 3 (of 3 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You *might* be able to use FMTONLY but the underlying problem is that there is no "contract" from the systems perspective of what the output of a stored procedure will be. In reality a sproc could have one, multiple, or no output and that can vary depending on the internal login. It is unfortunate but I know of know way to get output signature without hand documenting it and all variations.

Sorry..

CEWII

Dave Mason SSCrazy Points: 2751 More actions · Answer 2

Thanks, Elliott. FMTONLY may work, although some initial testing with some various sp's gives me some errors. It should be pretty easy to get what I need via the .NET framework and C#. 🙂

Dave MasonSeminole County, FL

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I have to say be careful using that method, depending on how the sproc was written the output can be wildly different from one run to another depending on input parameters. Unless the developer planned ahead and made sure that all code paths generate the same output you can't guarantee it..

CEWII