Mysql – Returning true or false in a custom function

functionsMySQL

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?.

Best Answer

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.

Related Question