mysql declare variable syntax error

Question

I am trying to migrate a scalar valued function of MS SQL to function in MySQL and have been using the following syntax as Queries to create function named xx4.

DROP FUNCTION IF EXISTS `xx4`;`
CREATE DEFINER = CURRENT_USER FUNCTION `xx4`(code VARCHAR(3))`
RETURNS CHAR`
BEGIN
`DECLARE coden INT UNSIGNED DEFAULT 0= CAST(CODE AS UNSIGNED)`
    `DECLARE ret CHAR =CASE` 
    `when coden<50 then 'A'` 
    `when coden<100 then 'B'` 
    `when coden<350 then 'C'` 
    `when coden<360 then 'D'` 
    `else null` 
`END`
`RETURN ret`
`END;

It gives me error as:

at Declare ret char=Case when coden<50 then 'A' when coden<100 w' at line 4

Can you please tell me where I am wrong? Appreciate your help.

I think it needs further edit as the syntax appears with too many symbol of ` . — Abdullah Mamun
– Abdullah Mamun, Commented Oct 3, 2016 at 21:11
default 0=? what's that supposed to be? trying to set coden to be the boolean result of 0=cast(...)? default values can't be expressions. — Marc B
– Marc B, Commented Oct 3, 2016 at 21:17

udog · Accepted Answer · 2016-10-03 21:24:30Z

1

I'm not sure why there are so many backticks, but the following syntax works (mind your semicolons):

DROP FUNCTION IF EXISTS xx4;

DELIMITER $$

CREATE DEFINER = CURRENT_USER FUNCTION xx4(`code` VARCHAR(3))
RETURNS CHAR
BEGIN
DECLARE coden INT UNSIGNED DEFAULT 0 = CAST(`CODE` AS UNSIGNED);

    DECLARE ret CHAR;

    set ret = CASE 
    when coden<50 then 'A' 
    when coden<100 then 'B' 
    when coden<350 then 'C' 
    when coden<360 then 'D' 
    else null    
    END;

RETURN ret;
END;$$

DELIMITER ;

edited Oct 3, 2016 at 21:24

answered Oct 3, 2016 at 21:15

udog

1,5464 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CodeLikeBeaker Over a year ago

I would suggest adding ` around code as it's a keyword.

udog Over a year ago

Done, as Jason Heine suggested

Abdullah Mamun Over a year ago

I ran your syntax but returns with error at line 4.

udog Over a year ago

No problem. Please accept the answer at your convenience.

Abdullah Mamun Over a year ago

Hi udog, where do I accept the answer? I couldn't fine the option.

|

Collectives™ on Stack Overflow

mysql declare variable syntax error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related