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:57:29 -0800

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: Determining column names and types of recordset returned by stored procedure
    ... There is no such contract to define what a stored procedure will return. ... > I understand that a proc may return no recordset, one recordset, or ... > built in procs) to get such info about a table as follows. ... > transaction and passing all null parameters, ...
    (microsoft.public.sqlserver.programming)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Not Getting results from Stored procedure
    ... So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. ... In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. ... These messages appear to be returned to the calling application as a recordset. ... when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method. ...
    (microsoft.public.vb.database)
  • Re: vbscript logon script getting return data from sql sp
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (microsoft.public.scripting.vbscript)
  • Re: Missing Resultset When Calling an Stored Procedure w/a Nested
    ... we tried advancing to the next recordset by using the NextRecordSet ... program only returned the nested stored procedure call resultset and failed ...
    (microsoft.public.data.ado)
Loading