Re: SQL Stored Procedure problem

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I think John found your problem, so let me look at something else...

As a training exercise, this process is ok, but I wonder if you really
need the cursor and the loop...

Remember my motto: If you're using a cursor your _probably_ doing
something wrong.

SQL is designed to work with sets of records, most of the time,
whatever you're doing with a cursor you can do in a single statement.

CREATE PROCEDURE UPDMHGPI
   LANGUAGE SQL
   MODIFIES SQL DATA
   BEGIN
     update EMRMEDS
         set MDMEDGPI = (select FNEWGPI
                                       from FACTS
                                       where FGPI = MDMEDGPI
                                    )
   END


The only issue you should have with the above is MDMEDGPI will need to
be NULL capable if a matching row doesn't exist in FACTS.  There are a
couple of ways to work around that:
1) Use coalesce() function
2) using EXISTS predicate

Examples of both methods should be in the archives.  I'd use
coalesce() if you expect most rows to have a matching value and EXISTS
if you expect most to not have a matching value.

HTH,
Charles Wilt



On Sun, Mar 15, 2009 at 7:58 PM, Michael Soucy
<michaelsoucy@xxxxxxxxxxxxxxx> wrote:
> 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.