Unable to create UDF in my sql

Question

Unable to create UDF. Am getting below error at the time of creation

Error: 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 ';
CREATE  FUNCTION `seat_count`(seatno varchar(250))
RETURNs varchar(250) 
BEG' at line 1

code: http://pastebin.com/uf4DkXZh

code:

CREATE  FUNCTION `seat_count`(seatno VARCHAR(250))
RETURNS VARCHAR(250) 
BEGIN
SET @str= REPLACE(seatno, ',', '');
SET @stcnt=CHAR_LENGTH(str); 
SET @x=1;
SET @result;
WHILE (@x <= @stcnt)
DO
    SET @st=SUBSTRING_INDEX(@str,',',X);
    IF ((LENGTH(@seatno) - LENGTH(REPLACE(@seatno, st, '')))/LENGTH(@st)=1)
        THEN SET @result=CONCAT(@st,@result);

     END IF;
     SET @x=@x+1;
END WHILE;
RETURN @result;

END$$

The code is not too long to be added to your question, so don't use pastebin, but add it here. And maybe write some words as in introduction, like `I'm trying to create a UDF in MySQL but I get this error:`. Looks a lot nicer already. To your problem: Remove the `;` in `$$;` and add `$$` after the last `END` and try again (didn't check the rest, so maybe there are other problems too). — Solarflare, Nov 21 '16 at 10:08

score 1 · Accepted Answer · edited May 23 '17 at 12:24

When you develop a stored procedure in MySQL, you can pass the input parameters and declare the local variables, the local variable are not prepended with any prefixes unlike user-defined variable which are prepended with @. For detail procedure vs user-defined variable

DELIMITER $$
    CREATE FUNCTION `SEAT_COUNT`(`seatno` VARCHAR(250)) RETURNS VARCHAR(250)
        DETERMINISTIC
    BEGIN

        DECLARE result VARCHAR(250);
        SET @str = REPLACE(seatno, ',', '');
        SET @stcnt = CHAR_LENGTH(@str); 
        SET @x = 1;

            WHILE (@x <= @stcnt)
            DO
                SET @st = SUBSTRING_INDEX(@str,',',X);
                IF ((LENGTH(@seatno) - LENGTH(REPLACE(@seatno, st, '')))/LENGTH(@st)=1)
                    THEN SET result = CONCAT(@st, @result);
                END IF;
                SET @x = @x+1;
            END WHILE;

        RETURN result;

    END$$
    DELIMITER ;

You have defined the result variable as user-defined e.g SET @result instead of local variable e.g DECLARE result VARCHAR(250)

I hope this will work at your end too.

Unable to create UDF in my sql

1 Answers1