Stored procedure to check if username exists 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
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 &gt; 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 &gt; 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 !!!