MySQL function getting syntax error query does not return

Question

I am new to mysql function and store procedure.Here is my function of mysql with function name create_role that takes argument role_name.What I have to do is If there is any row with with particular role name then it should return 1 or true, if there is no role then it should return 0 or false.

CREATE FUNCTION create_role ( role_name VARCHAR(100))
RETURNS INT
BEGIN
    DECLARE check_role INT;
    SET check_role = 0;
    SELECT * from role where role.role=role_name into check_role;
    RETURN check_role;
END

You have to use delimiter // at the beginning and // at the end of the function definition. — Giorgos Betsos
– Giorgos Betsos, Commented Apr 3, 2018 at 5:33

Lukasz Szozda · Accepted Answer · 2018-04-03 05:31:42Z

1

You could use IF and EXISTS:

CREATE FUNCTION create_role ( role_name VARCHAR(100))
RETURNS INT
BEGIN
    IF EXISTS (SELECT * from role where role.role=role_name) THEN
       RETURN 1;
    ELSE 
       RETURN 0;
    END IF;
END

answered Apr 3, 2018 at 5:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

juergen d · Accepted Answer · 2018-04-03 05:31:06Z

0

How about

SET check_role = (SELECT count(*) > 0 from role where role = role_name);

answered Apr 3, 2018 at 5:31

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

MySQL function getting syntax error query does not return

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related