Insertion error - Stored Procedure

Database

Insertion error - Stored Procedure

Hi,

I am very new to stored procedure and I need some clarification on the code which I have attached.

DELIMITER $$

CREATE PROCEDURE insertDb(IN record_state SMALLINT(6))

BEGIN
DECLARE record_state_val INTEGER;
DECLARE CONTINUE HANDLER for 1062
BEGIN

SET record_state_val = record_state;
SET record_state_val = record_state_val + 1;
SET record_state = record_state_val;

END;

INSERT INTO IMRecordTable (RECORD_STATE) VALUES (record_state);

COMMIT;

END $$
DELIMITER ;

Basically I am trying to avoid the duplicate records here. Now the problem is when I do an insertion with record_state as '1' continue handler will catch it and will increment the record_state to '2' and after that it ends up. It doesn't do the insert again. The solution I am looking for is when record_state '1' it needs to get incremented to 2 and try to insert again. If record_state '2' already exists it needs to increment to '3' and again do the insertion. Now to do this? How to call the insert again from the continue handler.

Tags (2)
1 REPLY
Senior Apprentice

Re: Insertion error - Stored Procedure

Why do you post questions related to mysql on a Teradata site?

Nevertheless this is not going to work in any DBMS, a CONTINUE handler continues with the next statement, but doesn't repeat the failing one.