Stored Procedure Question

Jay:

Good timing, we just figured out the two cursor thing a couple of weeks back.  
We are using embedded SQL in RPGLE for our stored procedures, but it should be 
the same.  This allowed us to return two cursors and two result sets to the 
calling VB program using an OLE/DB connection.

Hope it helps.

C/EXEC SQL INCLUDE SQLCA
C/END-EXEC

C/EXEC SQL DECLARE C1 CURSOR FOR
C+ SELECT       TLPRD,
C+              TLSEQ,
C+              TLTCD,
C+              TDTCD,
C+              TDDES,
C+              FROM (TLPTL INNER JOIN TLPTD ON
C+              TLTCD = TDTCD)
C+              WHERE
C+              (TLPRD = :pPRD) AND                     THIS is a host 
variable--as400
C+              (TLSF <> 'D')
C+ ORDER BY     TLSEQ
C/END-EXEC

C/EXEC SQL DECLARE C2 CURSOR FOR
C+ WITH TOOLCODE
C+            ( TLPRD,
C+              TLTCD,
C+              TDTCD )
C+  AS ( SELECT TLPRD,
C+              TLTCD,
C+              TDTCD
C+              FROM TLPTL INNER JOIN TLPTD ON
C+              TLTCD = TDTCD
C+              WHERE
C+              (TLPRD = :pPRD) AND                   THIS is a host 
variable--as400
C+              (TLSF <> 'D'))
C+ SELECT       TLTCD,
C+              TCSEQ,
C+              TCCMT
C+              FROM (TOOLCODE INNER JOIN TLPTC ON
C+              TLTCD = TCTCD)
C+              WHERE
C+              (TLTCD = TCTCD) AND
C+              (TCSF <> 'D')
C+ ORDER BY     TLTCD, TCSEQ
C/END-EXEC

C/EXEC SQL OPEN C1
C/END-EXEC

C/EXEC SQL OPEN C2
C/END-EXEC

C/EXEC SQL SET RESULT SETS CURSOR C1, CURSOR C2
C/END-EXEC


David Smith
Tri-State Hospital Supply Corp.
IT Consultant
dsmith@tshsc.com
517.546.5400


>Message: 12
>From: "Jay Himes" <j@liberty.edu>
>To: <midrange-L@midrange.com>
>Subject:  Stored Procedure Question
>Date: Thu, 10 Oct 2002 08:49:15 -0400
>Reply-To: midrange-l@midrange.com

>I am trying to create a SQL stored procedure. I need to open 2 cursors for
>selects from 2 different tables (I cannot join these tables together to get
>the results I want). I have included an example of what I am trying to do
>(yes, I know it doesn't do anything but the actual code is about 350 lines).

>When I try to run this statement I get the error:
>SQL0199  30      21  Position 11 Keyword DECLARE not expected. Valid tokens:
>                     ( IF GET SET CALL CASE DROP GOTO LOCK OPEN WITH ALTER
>                     CLOSE.
>for the line where the second DECLARE CURSOR statement is.
>
>I have tried declaring both cursors at the beginning of the procedure; but
>that has not
>worked either. It does work if I take out all the code referencing the
>second cursor.
>I am using V5R1.