CODEWITHSUNDEEP
mysqlfunctionworkbench
Christian Karlsson
Christian Karlsson

Reputation: 13

MYSQL function, is it Workbench or just noobish me?

I have been sitting with a stored procedure for MySQL for days now, it just won't work, so I thought I'd go back to basic and do a very simple function that checks if an item exists or not.

The problem I had on the first one was that it said END IF is invalid syntax on one of my IF clauses, but not the other two. The second one won't even recognize BEGIN as valid syntax...

Is it I that got everything wrong, or have I stumbled upon a MYSQL Workbench bug? I have Workbench 5.2 (latest version when I'm writing this) and this is the code:

DELIMITER $$

CREATE FUNCTION `filmsidan`.`f_lateornot` (movie_id INT)
BEGIN
    DECLARE check_val INT;
    DECLARE return_val INT;

    SELECT stockId
    FROM orders
    WHERE stockId = movie_id
    INTO check_val;

    IF check_val <= 0
    THEN
        SET return_val = 1;
    ELSE
        SET return_val = 0;
    END IF;

    RETURN return_val;

END

Upvotes: 0

Views: 3109

Answers (3)

Prasad Phule
Prasad Phule

Reputation: 478

DELIMITER $$

CREATE FUNCTION `filmsidan`.`f_lateornot` (movie_id INT)
BEGIN
    DECLARE check_val INT;
    DECLARE return_val INT;

    SELECT stockId
    FROM orders
    WHERE stockId = movie_id
    INTO check_val;

    IF check_val <= 0
    THEN
        SET return_val = 1;
    ELSE
        SET return_val = 0;
    END IF;

    RETURN return_val;

END

$$

DELIMITER ;

Add this last thing it works :

$$

DELIMITER ;

it means you are using ( ; ) this in function so for that reason we use it..see

and see also MySQL - Trouble with creating user defined function (UDF)

Upvotes: 0

Rahul
Rahul

Reputation: 77876

You have to specify the return value in signature as well delimiter at the end is missing. So, your function should look like

DELIMITER $$  
CREATE FUNCTION `filmsidan`.`f_lateornot` (movie_id INT) RETURNS INT
BEGIN  
DECLARE check_val INT;     
DECLARE return_val INT;      
SELECT stockId     
FROM orders     
WHERE stockId = movie_id     
INTO check_val;      
IF check_val <= 0     
THEN         
SET return_val = 1;     
ELSE         
SET return_val = 0;     
END IF;      
RETURN return_val;  
END
$$

Upvotes: 1

Tobias Bambullis
Tobias Bambullis

Reputation: 249

to fix the "begin" syntax error, you have to declare a return value, like this:

CREATE FUNCTION `filmsidan`.`f_lateornot` (movie_id INT) RETURNS INT(11)

after doing that, Workbench won't return an error anymore ;o)

Upvotes: 1

Related Questions