Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: updating in stored procdure problem

    Hello, I am creating the stored procedure which can update the lastname of employee using the for loop control,but when i update the lastname it doesn't change data lastname,can you help me please on this.Thank you in advance.


    CREATE PROCEDURE SEARCH_ID (IN EMP_ID CHAR(6),
    IN E_LASTNAME VARCHAR(30))




    BEGIN

    DECLARE EMPID CHAR(6);
    DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
    DECLARE CUR_SEARCH CURSOR WITH RETURN FOR

    SELECT EMPNO
    FROM EMPLOYEE;

    OPEN CUR_SEARCH;

    FOR ROW_VAR AS CUR_SEARCH CURSOR FOR

    SELECT LASTNAME FROM EMPLOYEE
    DO
    FETCH CUR_SEARCH INTO EMPID;
    IF EMPID=EMP_ID THEN
    UPDATE EMPLOYEE
    SET LASTNAME = E_LASTNAME
    WHERE EMPNO = EMP_ID;
    END IF;
    END FOR;

    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT ='EMPLOYEE I.D DOES NOT EXIST';




    END

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That seems to be a convoluted way to update just 1 row. Why not just use a simple update statement and a condition handler to let the caller know if the emp_id does not exists?

    Andy

  3. #3
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    That seems to be a convoluted way to update just 1 row. Why not just use a simple update statement and a condition handler to let the caller know if the emp_id does not exists?

    Andy

    Hi Andy,

    Thank you for the reply,I just want to try to use the for loop.can you help me please.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why would you want to loop through all the rows of a table to update one row?

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I also don't understand why you would want to use a loop. (Using procedural SQL where it is not necessary/helpful is a real problem in many situations.)

    However, you may want to look at your cursor definitions. EMPID is a variable of type CHAR(6). If I'm not mistaken, then your loop fetches LASTNAME into that variable. The IF-condition will fail on that so that no UPDATE is done at all. I'd give all cursors a unique name to avoid such problems.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by stolze View Post
    I also don't understand why you would want to use a loop. (Using procedural SQL where it is not necessary/helpful is a real problem in many situations.)

    However, you may want to look at your cursor definitions. EMPID is a variable of type CHAR(6). If I'm not mistaken, then your loop fetches LASTNAME into that variable. The IF-condition will fail on that so that no UPDATE is done at all. I'd give all cursors a unique name to avoid such problems.

    hello,thank you for the reply,okay sir thank you for correcting my if statement,but sir how can i make the stored procedure that will check if the employee id is exist on the table then make an update and signal if the employee id does not exist.i really want to know how.please help me i am still beginner...Thank you in advance...more power to you.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think I told you how to do it in my first post. Just create a condition handler if the row is not found that returns the SQLSTATE that you want and just issue the update statement.

    Andy

  8. #8
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    Why would you want to loop through all the rows of a table to update one row?

    Andy
    Hello sir, I want to make a loop so that i can use signal and to display if the employee does not exist and make an update if the employee id is found...Okay sir i know that you know that my stored procedure is wrong.I just want to make a stored procedure that will update if the employee id is found and else if employee id does not exist and display employee does not exist...by the way sir i am using ibm data studio...can you help me how am i going to make this update and display if does not exist...hope you will help.more power to you.Thank you in advance.

  9. #9
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    I think I told you how to do it in my first post. Just create a condition handler if the row is not found that returns the SQLSTATE that you want and just issue the update statement.

    Andy

    Hi sir Andy,

    sir can you help me on condition handler i really don't know the condition handler how to make that. That's why i find difficulty to do what you told me...can you please show some example sir?Thank you in advance.more power to you...

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have you tried reading the manual?

    Andy

  11. #11
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    Have you tried reading the manual?

    Andy

    yes sir but i am confuse. this is the link sir correct me if i am wrong.
    http://publib.boulder.ibm.com/infoce...d/c0009027.htm

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using? What is so confusing?

    Andy

  13. #13
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    What DB2 version and OS are you using? What is so confusing?

    Andy

    IBM® Data Studio Release 2.2.1.0,O.S(windows xp 2)...in using the handler types sir i am confuse i am looking at the example but still i could not get the idea.

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I meant what DB2 version, not what Data Studio version. And what OS does DB2 run on--not Data Studio? I got that you are confused, but be specific as to what is confusing you. Just telling me you are confused over and over gets you no where.

    Andy

  15. #15
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    I meant what DB2 version, not what Data Studio version. And what OS does DB2 run on--not Data Studio? I got that you are confused, but be specific as to what is confusing you. Just telling me you are confused over and over gets you no where.

    Andy
    DBV9.7.400.501, Okay sir here is my code.i am confuse in using the handler it's behavior the handler type and the conditions.where am i going to put the message "id no does not exist"?.please help me.more power to you always.

    CREATE PROCEDURE SEARCH_ID (IN EMP_ID CHAR(6),
    IN E_LASTNAME VARCHAR(30))




    BEGIN

    DECLARE EMPID CHAR(6);
    DECLARE EXIT HANDLER FOR NOT FOUND;

    UPDATE EMPLOYEE
    SET LASTNAME=E_LASTNAME
    WHERE EMPNO = EMP_ID;


    END

Posting Permissions

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