MYSQL Function - Syntax Error

Hi,

I'm a newbie in MYSQL. I'm trying to create this small function which takes 2 parameters - 'Table Name and Column name' and returns the (max ID +1) of the column from the table. Basically it is returning the max+1 of primary key.

But the catch is my primary keys are Varchars. It has 2 letters in the beginning and then the number. so I'm trying to get the number substring and increment it and then concatenate string and incremented number and return it back.

I'm getting this weird syntax error for this function and I'm not able to debug it.


Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= substr(val,1, 2);
intval= substr(val,3);
intval= intval + 1;
val =' at line 7


I have checked the syntax using a select statement and it works fine. Bt i don;t know what is happening here.

Can anyone give me any idea what is wrong? Thanks.


here is the function:


DELIMITER $$

DROP FUNCTION IF EXISTS `perepository2`.`Get_Max_Id` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `Get_Max_Id`(TabName Varchar(50),ColName Varchar(50)) RETURNS varchar(50) CHARSET latin1
BEGIN
Declare val, charval varchar(50);
Declare intval int;

Select max(ColName) from TabName into val;
charval= substr(val,1, 2);
intval= substr(val,3);
intval= intval + 1;
val = concat(charval, intval);
RETURN val;
END $$

DELIMITER ;

Cool....Nobody replied. well i got the answer myself. But for anyone who may have the same problem in future, here is the solution.

You have to use SET before assigning a value to a variable. From my above code instead of saying:

charval= substr(val,1, 2);

we have to say:

SET charval= substr(val,1, 2);

and same goes with any assignment statement. This a MYSQL thing may be.

- Vamsi