Returning true or false in a custom function

Question

I have this mysql table schema

CREATE TABLE `nxt_records` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tel_number` VARCHAR(50) NULL DEFAULT NULL,
    `nxt_status` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

and this function

DELIMITER $$
CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar
BEGIN 
 if (exists(select tel_number from nxt_records where tel_number = in_number ))then
return 'true';
else
return 'false';
end if;
END$$
DELIMITER ;

which i want to use inside this stored procedure

DELIMITER //
CREATE PROCEDURE nxt()
BEGIN
if (the_nxt(44) = true)then

select 'found.';
else
select 'nothing found.';
end if;

END //
DELIMITER ;

when i run the function code,i get the error

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN if (exists(select tel_number from nxt_records where tel_number = in_num' at line 2

What is causing this error?.

Answer 1

I found the error to have been my varchar

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar

which i changed to

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.

Answer 2

Use this

CREATE FUNCTION the_nxt (in_number VARCHAR(10)) RETURNS varchar(10) BEGIN if (exists(select tel_number from nxt_records where tel_number='in_number' )) then return 'true'; else return 'false'; end if; END$$

Faced this error ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) and use this SET GLOBAL log_bin_trust_function_creators = 1;

Regards, Nawaz