By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Stored Procedures Updates

P: 1
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,
Sep 5 '07 #1
Share this Question
Share on Google+
1 Reply


P: 9
My friend:

You have to use an Exception Handle to make that happen, cause is not enough declare SQLSTATE = 100.

Remi
Sep 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.