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

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.

Why would you want to loop through all the rows of a table to update one row?

Andy

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.

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

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.

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...

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

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.

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