CODEWITHSUNDEEP
mysqlsql
Maris S.
Maris S.

Reputation: 61

MySQL return true on label LIKE matched string

Im facing some problems creating MySQL script.

The idea is to pass 2 variables to the function, one on them is string, which is used to search for 'LIKE' type of match in name or surename column, the second variable passed is foreign key as integer, which is meant to match an id column in table im trying to check the match.

Long story short i need to return true if name or surename is 'LIKE' passed string and the id of the exact row is like passed foreign key value in parameters. Stored function must return true or false at the end of execution.

My script looks like this so far :

CREATE DEFINER = CURRENT_USER FUNCTION `is_persona`(`searchq` varchar(100),`fk_key` int(11))
RETURNS integer
DETERMINISTIC
BEGIN
SELECT
CASE WHEN name LIKE searchq THEN 1 ELSE 0  END AS 'result',
CASE WHEN surename LIKE searchq THEN 1 ELSE 0 END AS 'result'
FROM personas WHERE id = fk_key;
END;

And so far this script returns error "1415 - Not allowed to return a result set from a function".

Thanks for any help.

Upvotes: 0

Views: 757

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

The error is because you have to assign the select result into a variable. You can try something like below. Use your IF conditions accordingly.

CREATE DEFINER = CURRENT_USER FUNCTION is_persona(searchq varchar(100),`fk_key` int(11))
RETURNS varchar(20)
DETERMINISTIC
BEGIN
DECLARE MYOUTPUT1 varchar(20);
DECLARE MYOUTPUT2 varchar(20);
SELECT CASE WHEN name like concat('%',searchq,'%') THEN 1 ELSE 0  END AS 'result',
       CASE WHEN surename like concat('%',searchq,'%')  THEN 1 ELSE 0 END AS 'result'
INTO MYOUTPUT1,MYOUTPUT2 --you are missing this part
FROM personas WHERE id = fk_key;

IF(MYOUTPUT1 =1 && MYOUTPUT2 =1 ) then
RETURN 'TRUE';
ELSE 
RETURN 'FALSE';
END IF;
END;

CHECK DEMO HERE

Update for function with boolean return type

MySQL does not have built-in Boolean type. However, it uses TINYINT(1) instead. To make it more convenient, MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1).

CREATE DEFINER = CURRENT_USER FUNCTION is_persona(searchq varchar(100),`fk_key` int(11))
RETURNS boolean
DETERMINISTIC
BEGIN
DECLARE MYOUTPUT1 varchar(20);
DECLARE MYOUTPUT2 varchar(20);

SELECT CASE WHEN name like concat('%',searchq,'%') THEN 1 ELSE 0  END AS 'result',
       CASE WHEN surname like concat('%',searchq,'%')  THEN 1 ELSE 0 END AS 'result'
INTO MYOUTPUT1,MYOUTPUT2 
FROM personas WHERE id = fk_key;

RETURN IF((MYOUTPUT1 || MYOUTPUT2),true,false);

END;

DEMO FOR BOOLEAN RETURNING FUNCTION

Upvotes: 2

Related Questions