Board index » delphi » Returning a result set via cursor in SQL Server stored procedure

Returning a result set via cursor in SQL Server stored procedure

Does anyone know how to return a result set via a cursor in a SQL
Server stored procedure.  The reason I want to know is so that it can
be used in conjunction with a TStoredProc object.

At the moment, I am returning the result set to a TQuery object from a
stored procedure using a standard SELECT clause (i.e. without using
cursors).

Incidentally, has anyone noticed that a TStoredProc object extracts an
additional 'Params' element (index 0 that specifies the return value)
in SQL Server stored procedures than in Oracle?  Is this normal and
just another one of the differences between the two?

 

Re:Returning a result set via cursor in SQL Server stored procedure


The result set IS a (sort of) cursor and must be processed with TQuery.
TStoredProc can only grab values of OUTPUT parameters and as I'm sure you
know, their types must be one of the "standard" ones. In addition to OUTPUT
parameters, SQL Server procedures always return integer (usually 1 or 0).
This happens even if you didn't include RETURN 1/0/... in your procedure.
You may use it to indicate success or failure of any sort. SQL Server BOL
can tell you everything you need to know about this.

rb

Quote
"James Luc" <ja...@swallow.demon.co.uk> wrote in message

news:39d9c0aa.12562296@newsgroups.borland.com...
Quote
> Does anyone know how to return a result set via a cursor in a SQL
> Server stored procedure.  The reason I want to know is so that it can
> be used in conjunction with a TStoredProc object.

> At the moment, I am returning the result set to a TQuery object from a
> stored procedure using a standard SELECT clause (i.e. without using
> cursors).

> Incidentally, has anyone noticed that a TStoredProc object extracts an
> additional 'Params' element (index 0 that specifies the return value)
> in SQL Server stored procedures than in Oracle?  Is this normal and
> just another one of the differences between the two?

Re:Returning a result set via cursor in SQL Server stored procedure


Actually you can have TStoredProc return a result set.  All you have to do
is set Active := True instead of ExecProc.

Good luck,
krf

Re:Returning a result set via cursor in SQL Server stored procedure


Interesting, never tried this. Thanx.

Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:39db2648_2@dnews...
Quote
> Actually you can have TStoredProc return a result set.  All you have to do
> is set Active := True instead of ExecProc.

> Good luck,
> krf

Other Threads