All Forums Database
rakumark 1 post Joined 08/15
12 Aug 2015
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.

dnoeth 4628 posts Joined 11/04
12 Aug 2015

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.

Dieter

You must sign in to leave a comment.