Syntax Issue in Stored Procedure?

[random1]

Hey All,

 

The following runs fine:

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_admin_reset_autoincrement`(IN `tableName` VARCHAR(100), IN `incrementInteger` INT)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Resets the AUTO_INCREMENT value for a table'
BEGIN
ALTER TABLE tableName
AUTO_INCREMENT = 1;
END

 

But when I try changing "= 1" to "= incrementInteger" it fails:

 

 

Am I missing something really obvious? :S :B Is an auto_increment value strongly typed?

[kicken]

Based on the error I am going to guess that the value has to be a constant integer and not a variable.

 

That's not the only problem you have though.  You can't parameterize a table name like that.  Your procedure will try and alter quite literally the table called `tableName` and not whatever you pass in as that parameter.

 

Why are you trying to create this procedure anyway?  It's only a single line of SQL to do what you want to do, the procedure does not really save you anything.  I'd also question why your wanting to change the auto-increment value in the first place.

 

[fenway]

Yeah, variable table names are no fun -- it's hard to do because you're not supposed to do it.