INSERT query not running in MySQL stored procedure

Question

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.

Bill Karwin · Accepted Answer · 2017-11-30T21:05:03.840

3

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)

edited Nov 30 '17 at 21:05

answered Nov 30 '17 at 21:02

Bill Karwin

538,548
86
673
828

My `SELECT` statement is working and I'm getting the count. But the IF statement is not working. `_userCount` is giving the count. – Subrata Banerjee Nov 30 '17 at 21:04
@SubrataBanerjeesu the select does work, but it does not assign any value to your variable. Trust me, Bill is right. – Shadow Nov 30 '17 at 21:05
@Shadow let me try – Subrata Banerjee Nov 30 '17 at 21:07
So with @Bill's change the insert is working but I'm getting `_userInsertResult` as 0, even after successful insert. I'm using id as UUID – Subrata Banerjee Nov 30 '17 at 21:10
@SubrataBanerjee `LAST_INSERT_ID()` is only set for an ID assigned by auto-increment, not when you specify the value explicitly. – Barmar Nov 30 '17 at 21:18

score 0 · Answer 2 · answered Nov 30 '17 at 21:20

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

INSERT query not running in MySQL stored procedure

2 Answers2