CODEWITHSUNDEEP
mysqlstored-functions
cmal
cmal

Reputation: 2222

MySQL Error 1064 when create function

When I am trying to create a function in mysql 5.7.20, I met error 1064:

delimiter //
create function add_favorstocks (
uid_int INT,
stockid_char CHAR(20),
added_date CHAR(20)
)
returns INT
begin
    declare ret INT;
    case
    when exists (select 1 from user_favorstocks where uid=uid_int and stockid = stockid_char)
    then begin
         insert into user_favorstocks (uid, stockid, added_date) values (uid_int, stockid_char, added_date); 
         set ret=1;
         end
    else
        begin
            case (select is_deleted from user_favorstocks where uid=uid_int and stockid = stockid_char)
            when 0 then set ret=0;
            else begin
                update user_favorstocks set is_deleted=0, db_update_time=now() where uid=uid_int and stockid=stockid_char;
                set ret=3;
                end;
            end
        end
    end
    return ret
end//
delimiter ;

The error message is

ERROR 1064 (42000): 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 'else begin case (select is_deleted from user_favorstocks whe' at line 15

Does this because that I am using begin...end in an else clause?

I found the example in MySQL's documentation:

DELIMITER |
CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |

It seems valid to use a begin...end statement in an else clause.

Upvotes: 0

Views: 842

Answers (1)

wchiquito
wchiquito

Reputation: 16569

Try:

DROP FUNCTION IF EXISTS `add_favorstocks`;

delimiter //

create function add_favorstocks (
  uid_int INT,
  stockid_char CHAR(20),
  added_date CHAR(20)
)
returns INT
begin
    declare ret INT;
    case when exists (select 1
                      from user_favorstocks
                      where uid=uid_int and
                            stockid = stockid_char)
    then begin
         insert into user_favorstocks
           (uid, stockid, added_date)
         values
           (uid_int, stockid_char, added_date); 
         set ret=1;
         -- end <-- missing a semicolon
         end;
    else
        begin
          case (select is_deleted
                from user_favorstocks
                where uid=uid_int and
                      stockid = stockid_char)
          when 0 then set ret=0;
          else begin
              update user_favorstocks
              set is_deleted=0,
                  db_update_time=now()
              where uid=uid_int and
              stockid=stockid_char;
              set ret=3;
              end;
          -- end <-- missing CASE and a semicolon
          end case;
        -- end <-- missing a semicolon
        end;
    -- end <-- missing CASE and a semicolon
    end case;
    -- return ret <-- missing a semicolon
    return ret;
end//

delimiter ;

Upvotes: 1

Related Questions