make cursor out of second result set and discard the rest

make cursor out of second result set and discard the rest

Post by Theodore Hah » Fri, 06 Sep 2002 13:49:34



When processing the results of some stored procedures, the
desired results may be in the second or third result set.  
I would like to be able to turn one or all of the result
sets into cursors within a stored procedure of my own
creation.  The cursor syntax seems to want a select
statement, so I can insert data from the first returned
result set into a temporary table and then turn a select
statement on the temporary table into a cursor.  This
method only allows data from the first returned result set
to be used.  

A related problem is discarding the data sets that are not
needed so the user only receives one final result.  I
believe this problem can be solved by putting all the
possible result sets into cursors that may or may not be
used, but a more elegant solution is desired.

 
 
 

make cursor out of second result set and discard the rest

Post by oj » Fri, 06 Sep 2002 14:19:01


why not just scroll through the resultsets and present only the desired one
to the user. please take a look at Ado .NextRecordSet method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado2...
mdmthnextrec.asp

--
-oj
Rac v2.1 Public Beta is RELEASED.
http://www.rac4sql.net


Quote:> When processing the results of some stored procedures, the
> desired results may be in the second or third result set.
> I would like to be able to turn one or all of the result
> sets into cursors within a stored procedure of my own
> creation.  The cursor syntax seems to want a select
> statement, so I can insert data from the first returned
> result set into a temporary table and then turn a select
> statement on the temporary table into a cursor.  This
> method only allows data from the first returned result set
> to be used.

> A related problem is discarding the data sets that are not
> needed so the user only receives one final result.  I
> believe this problem can be solved by putting all the
> possible result sets into cursors that may or may not be
> used, but a more elegant solution is desired.


 
 
 

make cursor out of second result set and discard the rest

Post by Theodore Hah » Fri, 13 Sep 2002 08:23:22


I did not know you could use ADO inside a stored procedure
with TSQL commands.  Can you provide a sample or website
link that has more information?

-- Theodore

Quote:>-----Original Message-----
>why not just scroll through the resultsets and present

only the desired one
Quote:>to the user. please take a look at Ado .NextRecordSet
method:

>http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/ado270/htm/
>mdmthnextrec.asp

>--
>-oj
>Rac v2.1 Public Beta is RELEASED.
>http://www.rac4sql.net



>> When processing the results of some stored procedures,
the
>> desired results may be in the second or third result
set.
>> I would like to be able to turn one or all of the result
>> sets into cursors within a stored procedure of my own
>> creation.  The cursor syntax seems to want a select
>> statement, so I can insert data from the first returned
>> result set into a temporary table and then turn a select
>> statement on the temporary table into a cursor.  This
>> method only allows data from the first returned result
set
>> to be used.

>> A related problem is discarding the data sets that are
not
>> needed so the user only receives one final result.  I
>> believe this problem can be solved by putting all the
>> possible result sets into cursors that may or may not be
>> used, but a more elegant solution is desired.

>.

 
 
 

make cursor out of second result set and discard the rest

Post by linda deng[M » Fri, 13 Sep 2002 22:13:11


Hi Theodore,

I believe that oj wants to recommend you can execute the stored procedure
in ADO, then the method 'NextRecordset' can be used to process the result
set one by one, but you cannot use ADO inside a stored procedure with TSQL
commands. There are lots of Knowledge Base (KB) articles on describing how
to query several result sets returned from a stored procedure with VB
application, you can access the Web site http://support.microsoft.com with
the keywords 'stored procedure' and 'result set' as the search topics.

Sincerely,

Linda Deng
Microsoft Support Engineer

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

1. Hope Rule Is Willing To Discard Result Sets

Hi!

Pgsql complains the result returned by the called function being not used:

CREATE RULE MyRule AS ON INSERT TO MyTable DO SELECT MyFunction(NEW.c1)

It also suggests PERFORM if the returned value wants to be discarded.  However, there is no such syntax as:

CREATE RULE MyRule AS ON INSERT TO MyTable DO PERFORM MyFunction(NEW.c1)

I know the following combination of function and trigger achieves my goal:

CREATE FUNCTION tf() RETURNS TRIGGER AS '
BEGIN
  PERFORM MyFunction(NEW.c1);
  RETURN NEW;
END' LANGUAGE 'plpgsql'

CREATE TRIGGER MyTrigger AFTER INSERT ON MyTable FOR EACH ROW EXECUTE PROCEDURE tf();

Rule would be more concise than trigger + function in this case if it accepted unused result set.  This is only a better-to-have feature, I presume.

Regards,

CN
--
_______________________________________________
Get your free email from http://www.graffiti.net

Powered by Outblaze

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

2. Import to a memo Field

3. how to set sp_executesql result sets to a cursor

4. Pricing SQL Server 2000

5. Bug? where is the second empty result set

6. CLOSING ONE DATABASE

7. Result Sets vs copying result sets into memory.

8. listing columns in an Index...

9. Discarding Results from Within a Stored Procedure

10. Cursor result set limit of 1500?

11. SQL Server Result sets / cursors

12. Passing cursor/result set to extended stored procedure

13. SP result set in a cursor