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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 16:50:31 -0600

No answer for your exact problem, but have you tried using profiler to check
and see what is occurring. You might be able to mimic what it is doing. It
has been used many many times to solve this sort of thing. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Dave Hall" <dhall@nospam.deboy.com> wrote in message 
news:uiGWC0yAFHA.1188@tk2msftngp13.phx.gbl...
> Thanks Aaron,
>    I understand the problem you mention, and I understand that the results
> from any attempt to determine such info is not guaranteed accurate since, 
> as
> you put it, "there's no contract". Nonetheless, I see that the Visual 
> Studio
> 2003 Server explorer is reliably providing the information about
> storedprocedures that I'm trying to retreive, so I wonder how it's getting
> that information, even if it's just an educated guess rather than a
> contract.
>
> Dave
>
> "Aaron Weiker" <aaron@sqlprogrammer.org> wrote in message
> news:135990632422698471294730@news.microsoft.com...
>> 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