1

I am in trouble coding a store procedure as shown below

CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

The main issue is that MySQL Workbench outputs that there is an error at line:

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

But I can't figure out what is going on.

The fact is that: I am loading my database from hundreds of files and data is being stored in many different tables.

After I load each file, I trucate temporary table, because it is faster than use delete, so I load another file. When I trucate table, MySQL start from 0 the auto increment number, but I need last int number to use it again as I am using it as a primary key.

Does someone can help me?

1 Answer 1

1

Use a prepared statement

DELIMITER $$
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

SET @sql = "ALTER TABLE temp_imp AUTO_INCREMENT =  ?";
PREPARE stmt1 FROM @sql;
SET @a = _refID;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END$$
DELIMITER ;

If you are using workbench and create Stored procedure you don't need the DELIMITER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.