Re: Determining column names and types of recordset returned by stored procedure

From: Aaron Weiker (aaron_at_sqlprogrammer.org)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 14:10:47 -0800

Hello Dave,
There is no such contract to define what a stored procedure will return.
The same procedure can return 100 columns, just 5, or nothing at all. 

--
Aaron Weiker
http://aaronweiker.com/
> How can I determine the columns that will normally be returned from a
> particular stored proc without actually executing it?
> 
> I understand that a proc may return no recordset, one recordset, or
> multiple recordsets, but assuming that none of my procs return more
> than one recordset, is there a way I can determine the structure of
> that proc's resulting recordset without actually running it? For
> example, I can query sysobjects and syscolumns (or use any of several
> built in procs) to get such info about a table as follows.
> 
> select  sysobjects.Name as [TableName],
> syscolumns.Name as [ColumnName],
> systypes.Name as [TypeName],
> COLUMNPROPERTY ( object_id(sysobjects.name) , syscolumns.name ,
> 'Precision' ) as [Precision],
> COLUMNPROPERTY ( object_id(sysobjects.name) , syscolumns.name ,
> 'Scale' )
> as [Scale]
> from sysobjects (nolock)
> join syscolumns syscolumns (nolock)
> on syscolumns.id = sysobjects.id
> join systypes systypes (nolock)
> on systypes.xtype = syscolumns.xtype
> and systypes.xusertype = syscolumns.xusertype
> where sysobjects.Type = 'u'
> I'm looking for something roughly analagous to the query above, except
> changing the where clause to
> 
> where sysobjects.Type = 'p'
> 
> When I do this, it returns the parameters of the procs, but not any
> info about their resulting recordsets (if any).
> 
> As I said, I undersand why such a query would be problematic given
> that a proc could return any number of different recordsets each
> having different schemas depending on different criteria in the
> database or parameters of the proc. Even so. I see that the Visual
> Studio Server Explorer displays the exact info that I'm trying to
> retrieve, but it hopefully doesn't execute any of the procs in order
> to glean this info.
> 
> Any suggestions on how Visual Studio Servrer Explorer does it or how I
> might accomplish it? I thought about calling the procs within a
> transaction and passing all null parameters, then rolling back the
> transaction after I evaluate the recordset, but this seems way too
> complicated and could potentially affect the database if triggers were
> fired or identity fields incremented that the transaction didn't roll
> back.
> 
> I know it's do-able since VS 2003 is doing it, even if I have to make
> certain assumsions about the procs such as the assumsion that they
> don't return multiple recordsets. Can anyone help me get started in
> the right direction?
> 
> Thanks in advance.
> Dave

Relevant Pages