Error when set auto increment in prodceduce-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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.