Stored Procedure returns several recordsets

Results 1 to 3 of 3

Thread: Stored Procedure returns several recordsets

  1. #1
    Mark Guest

    Default Stored Procedure returns several recordsets

    I have a SQL Server stored proc that returns a number of recordsets (with PRINT statements in between each). I want to use these on an ASP page, but I&#039m not sure how to cope with (1) the PRINT statements, (2) the separate recordsets (which are of variable rowsize and number of columns).<BR>Any help greatly appreciated.

  2. #2 Guest

    Default RE: Stored Procedure returns several recordsets

    I don&#039t know much about sqlserver and stored procedures, but you use the Recordset.NextRecordset to scan multiple recordsets returned from a SP. Below is an extract from the MSDN library on the method:<BR><BR>NextRecordset Method (ADO)<BR> <BR><BR>Clears the current Recordset object and returns the next Recordset by advancing through a series of commands.<BR><BR>Syntax<BR><BR>Set recordset2 = recordset1.NextRecordset( RecordsAffected )<BR><BR>Return Value<BR><BR>Returns a Recordset object. In the syntax model, recordset1 and recordset2 can be the same Recordset object, or you can use separate objects.<BR><BR>Parameters<BR><BR>RecordsAffected Optional. A Long variable to which the provider returns the number of records that the current operation affected.<BR><BR>Remarks<BR><BR>Use the NextRecordset method to return the results of the next command in a compound command statement or of a stored procedure that returns multiple results. If you open a Recordset object based on a compound command statement (for example, "SELECT * FROM table1;SELECT * FROM table2") using the Execute method on a Command or the Open method on a Recordset, ADO executes only the first command and returns the results to recordset. To access the results of subsequent commands in the statement, call the NextRecordset method.<BR><BR>As long as there are additional results, the NextRecordset method will continue to return Recordset objects. If a row-returning command returns no records, the returned Recordset object will be empty; test for this case by verifying that the BOF and EOF properties are both True. If a non–row-returning command executes successfully, the returned Recordset object will be closed, which you can verify by testing the State property on the Recordset. When there are no more results, recordset will be set to Nothing.<BR><BR>If an edit is in progress while in immediate update mode, calling the NextRecordset method generates an error; call the Update or CancelUpdate method first.<BR><BR>If you need to pass parameters for more than one command in the compound statement by filling the Parameters collection or by passing an array with the original Open or Execute call, the parameters must be in the same order in the collection or array as their respective commands in the command series. You must finish reading all the results before reading output parameter values.<BR><BR>When you call the NextRecordset method, ADO executes only the next command in the statement. If you explicitly close the Recordset object before stepping through the entire command statement, ADO never executes the remaining commands.<BR><BR>Remote Data Service Usage The NextRecordset method is not available on a client-side Recordset object.<BR>

  3. #3
    Mark Guest

    Default RE: Stored Procedure returns several recordsets -

    Thanks for that. I&#039ll ty it....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts