MySQL – Stored Procedure to Check if Username Exists

MySQLstored-procedures

I'm trying to write a stored procedure

So far I have this, but it always returns 1

CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4)
SELECT CASE WHEN MAX(user_id) IS NULL THEN '0' ELSE '1' END User_exists
  FROM `dbname`.`tablename`
 WHERE `username` = USERNAME

The goal is to perform this via AJAX so the user can see whether registration is likely to succeed before submitting their registration form

Best Answer

Perhaps doing a COUNT is better since it must be a number always

DELIMITER $$
DROP PROCEDURE IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`
(IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4)
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    SELECT @User_exists;
END $$
DELIMITER ;

You could also write it as a function

DELIMITER $$
DROP FUNCTION IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `USER_EXISTS`
(`GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) RETURNS INT
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    RETURN @User_exists;
END $$
DELIMITER ;

Then, you could call the function to get a number

SET @x = USER_EXISTS('Synnyster');
SELECT USER_EXISTS('Synnyster');

Give it a Try !!!

Related Question