Cannot create MySQL function containing declare keyword

Question

I want to create a function like this:

CREATE FUNCTION fn_geturlparam (param varchar(55), url varchar(2048))  RETURNS varchar(2048) CHARSET utf8 COLLATE utf8_general_ci
  BEGIN

    DECLARE val VARCHAR(2048);
    DECLARE _param VARCHAR(60) DEFAULT CONCAT(param,'=');

    SELECT
      CASE
        WHEN locate(concat('&',_param), url) > 0
          THEN right(url, length(url) - (locate(concat('&',_param),url)+length(concat('&',_param))-1))
        WHEN locate(concat('?',_param), url) > 0
          THEN right(url, length(url) - (locate(concat('?',_param),url)+length(concat('?',_param))-1))
        WHEN locate(concat('#',_param), url) > 0
          THEN right(url, length(url) - (locate(concat('#',_param),url)+length(concat('#',_param))-1))
        WHEN locate(_param,url) > 0
          THEN right(url, length(url) - (locate(_param,url)+length(_param)-1) )
        ELSE null
      END
    INTO val;

    SET val = replace(replace(left(val, locate('&',concat(val,'&'))-1),'%20','         '),'+',' ');

    RETURN val;
  END

I tried everything but no luck. I'm using MySQL 5.5.15

This is the error:

#1064 - 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 '' at line 3

Answer 1

You need to provide a delimiter and also need to close the end using ;

delimiter //

CREATE FUNCTION fn_geturlparam (param varchar(55), url varchar(2048))         RETURNS varchar(2048) CHARSET utf8 COLLATE utf8_general_ci
 BEGIN

    DECLARE val VARCHAR(2048);
    DECLARE _param VARCHAR(60) DEFAULT CONCAT(param,'=');

    select
    case
        when locate(concat('&',_param), url) > 0
    then right(url, length(url) -         (locate(concat('&',_param),url)+length(concat('&',_param))-1))
        when locate(concat('?',_param), url) > 0
    then right(url, length(url) -         (locate(concat('?',_param),url)+length(concat('?',_param))-1))
        when locate(concat('#',_param), url) > 0
    then right(url, length(url) -         (locate(concat('#',_param),url)+length(concat('#',_param))-1))
        when locate(_param,url) > 0
            then right(url, length(url) - (locate(_param,url)+length(_param)-1) )
    else null
    end
    into val;

    set val = replace(replace(left(val, locate('&',concat(val,'&'))-1),'%20','         '),'+',' ');

    RETURN val;
  END;//

delimiter ;