Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Problem with Stored procedure in DB2

    Hi All, i am new to DB2 but after some RnD i have written this SP to accomplish my need. I am making some update in statement at some condition , in debug mode it comes to update only at those cndtns but when i see at end the update happens at all records for that field.

    Please let m eknow if i am doing something wrong?

    Thanks

    Prashant.

    Here is the SP

    CREATE PROCEDURE PRAGS.TESTOT ( )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable
    DECLARE ENDTABLE INT DEFAULT 0;
    DECLARE item_objid INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;
    DECLARE itm_id VARCHAR(10);
    DECLARE ec_mc_id VARCHAR(10);
    DECLARE s_itemi VARCHAR(10);
    DECLARE s_idcng VARCHAR(10);

    -- Declare cursor
    DECLARE cursor1 CURSOR FOR
    SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
    ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';

    -- Declare handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET ENDTABLE = 1;

    OPEN cursor1;

    SET ENDTABLE = 0;
    FETCH FROM cursor1 INTO item_objid, s_itemi, s_idcng;
    WHILE ENDTABLE = 0 DO
    SELECT COUNT(*) INTO num_rows FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = s_itemi AND EC_MC_ID = s_idcng;

    IF num_rows >= 1 THEN
    UPDATE PRAGS.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = item_objid AND S_IDCNG = s_idcng;
    END IF;

    FETCH FROM cursor1 INTO item_objid, s_itemi, s_idcng;
    END WHILE;
    CLOSE cursor1;
    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I would take a hard look at the data. I find it hard to believe that DB2 is just updating every row for the fun of it. I think it is doing exactly what you told it to do.

    Andy

Posting Permissions

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