Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    10

    Unanswered: Error : Stored Procedure For Updates

    Hi All,

    Can anyone please help me figure out the error with these stored Procedures.I am trying to perform large updates.The Updates are being performed but it goes into an infinite loop.

    CREATE PROCEDURE SAMPLE.SPROC (IN p_tablename varchar (50),
    IN p_IDNO integer)
    BEGIN
    DECLARE SQLCODE INTEGER ;
    DECLARE txt varchar (10000);
    DECLARE stmt varchar (10000);
    IF (p_IDNO IS NULL) THEN
    SET txt =
    'UPDATE (SELECT STATUS FROM ' || p_tablename || ' WHERE STATUS = ' || '''Y''' || ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
    ELSE
    SET txt =
    'UPDATE (SELECT STATUS FROM ' || p_tablename || ' where IDNO = ' || char(p_IDNO)|| ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
    PREPARE stmt FROM txt;
    l :
    LOOP
    EXECUTE stmt;
    IF SQLCODE = 100 THEN LEAVE l; END IF;
    COMMIT;
    END LOOP ;
    END IF;
    END


    This stored procedure returns an error and never returns any result.
    CREATE PROCEDURE SAMPLE.SPROC
    (IN tabschema VARCHAR(128),
    IN tabname VARCHAR(128),
    IN predicate VARCHAR(1000),
    IN commitcount INTEGER)
    BEGIN
    DECLARE SQLCODE INTEGER;
    DECLARE txt VARCHAR(10000);
    DECLARE stmt STATEMENT;
    SET txt = 'UPDATE (SELECT 1 FROM "'
    || tabschema || '"."' || tabname || '" WHERE '
    || predicate || ' FETCH FIRST ' ||
    RTRIM(CHAR(commitcount)) || ' ROWS ONLY) SET STATUS = ''' || 'N''';
    PREPARE stmt FROM txt;
    l: LOOP
    EXECUTE stmt;
    IF SQLCODE = 100 THEN LEAVE l; END IF;
    COMMIT;
    END LOOP ;
    END



    Thanks In Advance,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I can see where the first one can go into an infinite loop. When p_IDNO is not null, you will always have an infinite loop. You will keep fetching the same 5000 rows and setting the status to 'N' regardless of what the status is. And from the looks of it, it does nothing if if P_IDNO is null.

    My guess is that on the second SP, you have a similar situation where the where clause is selecting the same rows over and over.

    You need to add to the where clause of both: "AND STATUS <> 'N'"

    Andy

  3. #3
    Join Date
    Sep 2007
    Posts
    10
    Quote Originally Posted by ARWinner
    I can see where the first one can go into an infinite loop. When p_IDNO is not null, you will always have an infinite loop. You will keep fetching the same 5000 rows and setting the status to 'N' regardless of what the status is. And from the looks of it, it does nothing if if P_IDNO is null.

    My guess is that on the second SP, you have a similar situation where the where clause is selecting the same rows over and over.

    You need to add to the where clause of both: "AND STATUS <> 'N'"

    Andy
    Thanks for figuring out the mistake.I realised through your reply the mistake in the code which caused the loop to be infinite.

Posting Permissions

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