Question 21592 in Mysql Question posted in
The official MySQL documentation can be found here.

Error when set auto increment in prodceduce – Mysql

CREATE DEFINER = 'root'@'localhost'
PROCEDURE client_logging_system.Proc_client_Delete(IN in_clientID int)
  COMMENT '
-- Parameter:
-- in_clientID: ID of client
'
BEGIN
  DECLARE exit handler for sqlexception
  BEGIN
    ROLLBACK;
  end;
  
  START TRANSACTION;
  
  DELETE FROM `client` WHERE `client`.ID = in_clientID;
  ALTER TABLE `client` AUTO_INCREMENT = in_clientID;
  
  COMMIT;
END

My proceduce get error on line:

ALTER TABLE `client` AUTO_INCREMENT = in_clientID;

Any suggestion for this problem?

Tags:

2

Answers


  1. 0

    You can only use limited set of DDL statements inside a routine.

    What are you trying to achive with the ALTER TABLE-statement? If you delete a client with id that is not the biggest one, the ALTER TABLE would not make sense.

    Login or Signup to reply.
  2. 0

    You can’t use variables in ALTER statements, it needs a literal number there. You’ll need to create dynamic SQL using PREPARE.

    SET @st = CONCAT('ALTER TABLE `client` AUTO_INCREMENT = ', in_clientID);
PREPARE stmt FROM @st;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search