Re: SQL Stored Procedure problem

[John McKee <jmmckee@xxxxxxxxxxxxxx>]

I meant to write: I do not know how Last_Rec is to get a new value.

John McKee

Quoting John McKee <jmmckee@xxxxxxxxxxxxxx>:

> I have no experience with stored procedures.  So, I am curious why you are
> setting Last_Rec to 'Y'.  I would have set it to 'N'.  I do know how Last_Rec
> is to get a new value.  But, starting wiyth it as 'Y' look wrong.
>
> John McKee
>
>
> Quoting Michael Soucy <michaelsoucy@xxxxxxxxxxxxxxx>:
>
> Hello Everyone,
>
>    I'm having problem trying to write my 1st SQL Stored Procedure, and I
> thought I would post my problem here, and see if someone could help.  I'm
> trying to read a DB2 table, and update a second table based on a value in the
> 1st table.  When I run the SQL stored procedure it appears to only read one
> record, and then it leaves the loop.  Here is my code.
>
> CREATE PROCEDURE UPDMHGPI
>    LANGUAGE SQL
>    MODIFIES SQL DATA
>    BEGIN
>      DECLARE GPI CHAR(14);
>      DECLARE NEWGPI CHAR(14);
>      DECLARE Last_Rec CHAR(1);
>      DECLARE C1 CURSOR FOR SELECT FGPI, FNEWGPI FROM FACTS;
>      DECLARE CONTINUE HANDLER FOR NOT FOUND
>      SET Last_Rec = 'Y';
>      OPEN C1;
>      FETCH_LOOP:
>      LOOP
>       FETCH C1 INTO GPI, NEWGPI;
>       IF Last_Rec = 'Y' THEN
>        LEAVE FETCH_LOOP;
>       END IF;
>       UPDATE EMRMEDS SET MDMEDGPI = NEWGPI
>        WHERE MDMEDGPI = GPI;
>      END LOOP FETCH_LOOP;
>      CLOSE C1;
>    END
>
> I wrote this stored procedure based on an example I found in the book I have
> called "SQL for eServer i5 and iSeries" by Kevin Forsythe.  Can 
> anyone tell me
> where I've gone wrong?
>
> Sincerely,
> Michael Soucy
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.