MySql function not returning the correct result

Question

I'm trying to create a function in mysql which will return boolean if the id already exist in the table. Most of the solutions that I found online require solutions using both php and mysql but what I'm trying to do is a solution purely in mysql in the form of a function.

My simplifed mysql table will be as follow:

CREATE TABLE IF NOT EXISTS table1 (
    `user_id` VARCHAR(12) UNIQUE,
    `name` VARCHAR(128),
    PRIMARY KEY (`user_id`)
);

My first attempt was below:

DELIMITER *
DROP FUNCTION IF EXISTS CheckExistId*
CREATE FUNCTION CheckExistId (user_id VARCHAR(12)) RETURNS BOOL
BEGIN
    DECLARE isExist BOOL;
    SET isExist = 0;
    SELECT EXISTS(SELECT * FROM table1 WHERE `user_id`=user_id) INTO isExist ;
    RETURN isExist;
END*
DELIMITER ;

My second attempt:

DELIMITER *
DROP FUNCTION IF EXISTS CheckExistId*
CREATE FUNCTION CheckExistId (user_id VARCHAR(12)) RETURNS BOOL
BEGIN
    DECLARE isExist BOOL;
    DECLARE countRow INT;
    SELECT COUNT(`user_id`) INTO countRow FROM table1 WHERE `user_id`=user_id;
    IF countRow = 0 THEN SET isExist = 1; 
    ELSE SET isExist = 0; 
    END IF;
    RETURN isExist;
END*
DELIMITER ;

Both didn't give me the result that I expected. Any suggestions on how to fix this?

Could it be a problem that in #1 you use an asterisk as a delimiter, but then have an asterisk (star) in your select statement? Not sure - just guessing. — FreudianSlip
– FreudianSlip, Commented Aug 22, 2013 at 7:29
@kimsia nope. building a simple php/mysql system for office. — ImpStudent
– ImpStudent, Commented Aug 22, 2013 at 7:37
@ImpStudent In that case, can you help me understand why there is a need to do this purely in mysql? — Kim Stacks
– Kim Stacks, Commented Aug 22, 2013 at 7:38

fancyPants · Accepted Answer · 2013-08-22 08:46:03Z

3

Your first attempt was quite fine, except that you're using * as delimiter and using the same name for your parameter and column name. That's confusing MySQL. Write it like this:

DELIMITER $$
DROP FUNCTION IF EXISTS CheckExistId $$
CREATE FUNCTION CheckExistId (p_user_id VARCHAR(12)) RETURNS BOOL
BEGIN
    DECLARE isExist BOOL;
    SET isExist = 0;
    SELECT EXISTS(SELECT * FROM table1 WHERE `user_id`=p_user_id) INTO isExist ;
    RETURN isExist;
END $$
DELIMITER ;

answered Aug 22, 2013 at 8:46

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

2 Comments

ImpStudent Over a year ago

this works for me! i didn't know that we cant use same name for parameter and column name.. thanks!

fancyPants Over a year ago

Same story with parameter and column name. And shouldn't it be IF countRow = 0 THEN SET isExist = 0; ELSE SET isExist = 1;? or shorter IF countRow = 0 THEN RETURN 0; ELSE RETURN 1;

Kuzgun · Accepted Answer · 2013-08-22 07:53:09Z

0

DELIMITER *
DROP FUNCTION IF EXISTS CheckExistId*
CREATE FUNCTION CheckExistId (user_id VARCHAR(12)) RETURNS BOOL
BEGIN
SELECT 
    CASE 
        WHEN EXISTS(
             SELECT * FROM table1 WHERE `user_id`=user_id)
        THEN RETURN 1 

        ELSE RETURN 0 
    END
END*
DELIMITER ;

answered Aug 22, 2013 at 7:53

Kuzgun

4,7575 gold badges37 silver badges50 bronze badges

1 Comment

ImpStudent Over a year ago

seems like its not working for me. firstly the error with the delimiter. i changed it to // and test again. it now throws a syntax error, unexpected return_sym at "then return 1".

Anda Iancu · Accepted Answer · 2013-08-22 08:27:05Z

0

DROP TABLE IF EXISTS `TableTT2`;
CREATE TABLE IF NOT EXISTS TableTT2 (
    `user_id` VARCHAR(12) UNIQUE,
    `name` VARCHAR(128),
    PRIMARY KEY (`user_id`)
);

INSERT INTO `TableTT2` (`user_id`, `name`) VALUES
(LEFT(UUID(), 12), 'a1'), 
(LEFT(UUID(), 12), 'a2'), 
(LEFT(UUID(), 12), 'a3'), 
(LEFT(UUID(), 12), 'a4'), 
(LEFT(UUID(), 12), 'a5'), 
(LEFT(UUID(), 12), 'a6'), 
(LEFT(UUID(), 12), 'a7'), 
(LEFT(UUID(), 12), 'a8');


    DELIMITER $$

    CREATE FUNCTION `CheckExistId`(usrId varchar(12)) RETURNS BOOL
BEGIN

    DECLARE totalCnt INT;
    DECLARE resBool BOOL;
    SET totalCnt = 0 ;

    SELECT COUNT(*) INTO totalCnt FROM TableTT2 WHERE user_id = usrID; 
    SET resBool = IF(totalCnt <> 0, TRUE, FALSE);
    RETURN resBool;

END


SELECT * FROM TableTT2;
SELECT CheckExistId('fe3faa00-0b0');
SELECT CheckExistId('fe3faa00-111');

Hope this helps. It might be an issue using * delimiter and a second issue that i noticed your function parameter is a varchar even if you check by an integer id.

edited Aug 22, 2013 at 8:27

answered Aug 22, 2013 at 7:45

Anda Iancu

5301 gold badge3 silver badges9 bronze badges

2 Comments

ImpStudent Over a year ago

i was trying to understand ur code.. why is there two RETURN statement? by the way i corrected already that the id is supposed to be in varchar(12) and not int as i mentioned earlier.

Anda Iancu Over a year ago

my bad a typo for return statement and should work for vrachar i updated the code

Collectives™ on Stack Overflow

MySql function not returning the correct result

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related