MySQL Function - Error Code: 1415 Not allowed to return a result set from a function
28,491
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;
Author by
sqltracy
Updated on July 09, 2022Comments
-
sqltracy almost 2 years
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!
-
sqltracy about 11 yearsYou 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.