CODEWITHSUNDEEP
mysqlstored-procedures
Subrata Banerjee
Subrata Banerjee

Reputation: 299

INSERT query not running in MySQL stored procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `user_db`.`insertUser` $$
CREATE PROCEDURE `user_db`.`insertUser`(
    IN _userEmail VARCHAR(50),
    IN _userPassword VARCHAR(50),
    IN _userPhone VARCHAR(10),
    IN _userUsername VARCHAR(50),
    IN _userAccountType ENUM('0', '1'),
    IN _userAdmin ENUM('0', '1'),
    IN _userSuperAdmin ENUM('0', '1'),
    IN _userPresenceState ENUM('0', '1'),
    OUT _userInsertResult INT(36)
)
NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER
BEGIN
    DECLARE _userCount INT;

    SELECT COUNT(id) AS _userCount FROM `user_db`.`user_primary` WHERE email = _userEmail LIMIT 1;

    IF _userCount = 0 THEN

    INSERT INTO `user_db`.`user_primary` (id, email, encrypted_password, phone, username, user_account_type, user_admin, user_super_admin, presence_state) VALUES (UUID(), _userEmail, _userPassword, _userPhone, _userUsername, _userAccountType, _userAdmin, _userSuperAdmin, _userPresenceState);
    SET _userInsertResult := LAST_INSERT_ID();

    END IF;
END
$$
DELIMITER ;

I wrote this stored procedure in MYSQL to check if the user exist and if the count is 0 then insert and return the id else I'll do something else. But even if the _userCount is 0, the INSERT statement is not working. This is my first stored procedure and I'm learning. I'm trying but not able to understand the reason for failure.

Upvotes: 1

Views: 1042

Answers (2)

Uueerdo
Uueerdo

Reputation: 15941

An alternative answer is to tweak your logic a bit, replacing this

SELECT COUNT(id) AS _userCount 
FROM `user_db`.`user_primary` 
WHERE email = _userEmail LIMIT 1;

IF _userCount = 0 THEN

with

IF NOT EXISTS (SELECT * FROM `user_db`.`user_primary` WHERE email = _userEmail) THEN

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

You want:

SELECT COUNT(id) INTO _userCount FROM ...

Merely using AS just declares a column alias for the COUNT(id) expression, it doesn't assign the result to a variable of the same name as the alias.

So in your procedure, the variable _userCount is never assigned a value, and so the statement inside your IF block never runs.

P.S.: This has nothing to do with your question, but I noticed you are using INT(36). The argument to INT doesn't do anything. It doesn't make the INT larger or able to accept 36-digit numbers. See my answer to Types in MySQL: BigInt(20) vs Int(20)

Upvotes: 3

Related Questions