Programatic way or telling if a stored proc in SQL Server returns a result set?

  • Thread starter Thread starter wackyphill
  • Start date Start date
W

wackyphill

Using ADO.NET Or regular queries on SQL Server, is there a good way of
determining if a Stored Proc will be returning a result set or if it is
a non-query that does all I/O through parameters?

Thanks.
 
In order to do this, you would have to do an analysis of the actual SP
code. There really isn't a good way to do this without running the proc
itself.

Basically, a stored procedure can do both, set the return values through
parameters/the return value, and return a result set.

You can query the sysobjects table to get the id of the stored
procedure, then you should be able to query the syscolumns table (using the
id of the SP) to determine what parameters the stored proc has (filtering on
the id of the object). From there, you can tell if they are output values.

Hope this helps.
 
Thanks, I think I may be able to use the dataadapter to do the work
for me.
Click to expand...

which will do this under the hood:
SET FMTONLY ON
EXEC procedure
SET FMTONLY OFF

This will cause the proc to be executed but not really executed, it
will though return the resultset if any.

There are problems with this, so be careful:
- if the proc calls extended procs, like for sending mail, these will
be called
- if your proc uses a temp table, you'll get an exception.

there are other situations in which it will fail to produce proper
results.

Take it from me: there is no reliable way, other than ask the user for
the # of resultsets per proc.

FB



--
 
Back
Top