MySQL Function - Error Code: 1415 Not allowed to return a result set from a function

Question

I am not trying to return a result set and I have no idea what I'm doing wrong here. MySQL 5.5

delimiter $$

CREATE FUNCTION CheckAccount(
    i_username varchar(50)
) RETURNS integer

BEGIN

    DECLARE v_validUserId int;
    DECLARE v_validMembership int;
    DECLARE o_Status integer;

    SELECT vvalidUserId = u.UserId
    FROM Users u
    WHERE u.Username = i_username;

    IF( v_validUserId IS NULL ) THEN
        SET o_Status = 2; -- Invalid username
    ELSE
        SET o_Status = 1; -- Good
    END IF;


    IF(o_Status != 2 ) THEN
            SELECT v_validMembership = 1
            FROM Users u
            JOIN UserMemberships um on um.UserId = u.userId
            JOIN Memberships m on m.MembershipId = um.MembershipId
            WHERE um.MembershipExpireDateTime > CURDATE()
            AND u.UserId = v_validUserId;

            IF( v_validMembership IS NULL ) THEN 
                SET o_Status = 3; -- Invalid membership
            END IF;
    END IF;

    RETURN o_status;

END $$
DELIMITER ;

Any help will be greatly appreciated!

b.b3rn4rd · Accepted Answer · 2013-04-24 01:48:34Z

23

I'm not sure if you can assign variables that way, try use INTO statement for your selects. For example:

SELECT 
    u.UserId INTO vvalidUserId 
FROM 
    Users u
WHERE 
    u.Username = i_username;

answered Apr 24, 2013 at 1:48

b.b3rn4rd

8,8602 gold badges50 silver badges59 bronze badges

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

1 Comment

sqltracy Over a year ago

You are a GENIUS!!! Thank you SO MUCH! It was not showing that as an error so I would have never found that on my own.

Collectives™ on Stack Overflow

MySQL Function - Error Code: 1415 Not allowed to return a result set from a function

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related