Can i return a recordset in a Stored procedure

chiefouko · June 23rd, 2003, 04:06 AM

Hi Guys,

Is there a way I can return a recordset from a stored procedure. An example would help.

E.g (part of the procedure)

CREATE PROCEDURE Get_OrderRecords_By_Criteria (@fromDate smalldatetime,
@toDate smalldatetime, @type varchar(10) OUTPUT) AS

--Am not sure of this
DECLARE @Rst As Recordset

--If it's possible how would i get set the Rst to records returned
--below
Select fldDate,fldName
FROM tblOrderRecords
Where fldDate > @fromDate AND fldDate < @toDate

RETURN Rst

Please Help

LION OF JUDDAH!

Jeff Mason · June 23rd, 2003, 06:13 AM

A SELECT statement returns a resultset. A recordset is the object the client program's data provider (e.g. ADO) turns the resultset into when the SELECT statement is executed.

Thus, to return a resultset, simply execute a stored procedure containing a SELECT statement (e.g):

Code:

CREATE PROCEDURE Get_OrderRecords_By_Criteria
   @fromDate smalldatetime,
   @toDate smalldatetime,
   @type varchar(10) OUTPUT
AS
   set nocount on
   SELECT fldDate,fldName
      FROM tblOrderRecords
      WHERE fldDate > @fromDate AND fldDate < @toDate;

Using, say, an ADO command object in your client program will turn the resultset that executing the SELECT statement on SQL Server generated into an ADO recordset object which you can then manipulate in your client code.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com