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

From: Dave Hall (dhall_at_nospam.deboy.com)
Date: 01/26/05 

Date: Tue, 25 Jan 2005 22:02:10 -0500

Thanks Louis & Aaron.
Your suggestions about using profiler got me on track. It seems that VS
Server Explorer calls sp_procedure_params_rowset to get the parameters of
the proc then builds a command to be executed including dummy parameters
then wraps the call to the proc with SET NO_BROWSETABLE and SET FMTONLY
statements. Since the values don't matter, I use null in my version below
rather than worrying about data types of the dummy values.

The follwoing 5 lines are a stripped down version of the call to the proc as
shown in profiler.

SET NO_BROWSETABLE ON
SET FMTONLY ON
EXEC MyProc null , null -- add proper number of placeholder parameters here.
in this case, there are 2.
SET FMTONLY OFF
SET NO_BROWSETABLE OFF

An interesting note - In some procs which potentially return a variety of
schemas, the code above actually returns one empty recordset for each
possible code path, but all recordsets are empty. Visual Studio only
displays the first recrdset's schema in this case, but all are available.

For example, consider this proc which can return one of two recordsets with
each possible recordset having a different schema depending on the @mode
parameter: 

----
create procedure dbo.VariousTables ( @mode integer )
as
set nocount on
if @mode is null
  select 'Mode is null' as TextResults
else
  select @mode as IntegerResults, newid() as GuidResults
go
SET NO_BROWSETABLE ON
SET FMTONLY ON
EXEC VariousTables null
SET FMTONLY OFF
SET NO_BROWSETABLE OFF
go
drop procedure VariousTables
-----
In this case, the results include both tables, but no data in either table
as follows:
TextResults
------------ 
IntegerResults GuidResults
-------------- ------------------------------------ 
In this case, Visual Studio would only show TextResults as a resulting
column since it occurs first.
One last thing, if one proc calls another, this method still returns all
possible results of every code path including the nested proc. Pretty cool!
It looks like my original idea of calling the proc within a transaction and
roling it back wasn't too far off, but this provides a much better solution
besides the fact that I can now easily see any possible resulting schema.
It's better than I had hoped for.
Thanks again all.
Dave
"Aaron Weiker" <aaron@sqlprogrammer.org> wrote in message
news:137122632422726493538307@news.microsoft.com...
> Hello Dave,
> I think what it's doing is actually taking a look at the stored procedure
> and then tries to find out what values are being returned based upon the
> select statements.
>
> So the easiest thing to do would just be to open up VS and profiler and
see
> what is sent to the database.
>
> Someone where I work also mentioned that CodeSmith has a similar
functionality
> that you are looking for so you may want to try looking there for some
additional
> insight.
>
> --
> Aaron Weiker
> http://aaronweiker.com/
>
> > 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

  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... the original j a variant was because Longs and Integers by default are 0. ... And here is the related ADO code to invoke the Proc and get the "Records ... assigning the result to a Recordset object) try removing the SELECT ...
    (microsoft.public.data.ado)
  • How to get past an error?
    ... sql error but still need to return a recordset or print statement that ... statemets and depending on the type of error, might see the recordset.. ... in batchs.. ... PS try to make this proc send back the error,print and get the record ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Effects on RST in Calling Proc
    ... >> Obviously, the snapshot avoids that, but I think it's better to ... the recordset is snapshot and is passed to ... > the called proc so that another field can be searched for a value ... you avoid the issue entirely. ...
    (comp.databases.ms-access)
  • Re: Effects on RST in Calling Proc
    ... the original procedure in a way that is likely to survive the ... In the original proc, the recordset is snapshot and is passed to the called proc so that another field can be searched for a value that is being tested in the calling proc. ... I try as much as possible to avoid data manipulation by row processing and prefer set processing, ie, I prefer to execute an update or insert statement as opposed to going through each row in a recordset and doing ...
    (comp.databases.ms-access)
  • Assert failed-possible vs.net compatability issue
    ... I have both visual studio.net 2003 installed and visual studio 6 enterprise. ... Software engineer. ...
    (microsoft.public.dotnet.framework.aspnet)