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

    Unanswered: Strange behaviour in Stored Procedure

    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

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by cprash.aggarwal
    Can anyone tell me if i am doing something fundamentally wrong in procedure?
    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;
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by cprash.aggarwal
    UPDATE DB2ADMIN.LOGIN SET USER_PWD = 'Aggarwal' WHERE USER_ID = user_id;
    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 does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by n_i
    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

Posting Permissions

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