Hi , i am running this SP in db2 development center.

CREATE PROCEDURE PRAGS.TESTSP ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE ENDTABLE INT DEFAULT 0;

DECLARE user_id VARCHAR(10);
DECLARE user_pwd VARCHAR(10);

-- Declare cursor
DECLARE cursor1 CURSOR FOR
SELECT USER_ID, USER_PWD FROM DB2ADMIN.LOGIN;

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

OPEN cursor1;

SET ENDTABLE = 0;
FETCH FROM cursor1 INTO user_id, user_pwd;
WHILE ENDTABLE = 0 DO

IF user_id = 'Prashant' THEN
UPDATE DB2ADMIN.LOGIN SET USER_PWD = 'Aggarwal' WHERE USER_ID = user_id;
END IF;

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


I am running this simple procedure but i m facing very strange issue. In debug mode the control goes to Update stmnt only when user_id = 'Prashant' but when i see the table after the SP is run the update gets happen on all the records.

Can anyone tell me if i am doing something fundamentally wrong in procedure?

Thanks
Prashant

Yes: if I understand it well, you should replace the 4th last line
FETCH FROM cursor1 INTO item_objid, s_itemi, s_idcng;
by
FETCH FROM cursor1 INTO user_id, user_pwd;

SQL is not case-sensitive, and within the scope of the update statement user_id equals itself for all records, obviously. Now you know why naming local variables the same as the table columns is a bad idea.

It was really fantastic, i couldn't think to that point, thanks a lot it worked after changing the variable names.

Prashant