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-30 21:05:03Z

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, 2017 at 21:05

answered Nov 30, 2017 at 21:02

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

5 Comments

Subrata Banerjee Over a year ago

My SELECT statement is working and I'm getting the count. But the IF statement is not working. _userCount is giving the count.

Shadow Over a year ago

@SubrataBanerjeesu the select does work, but it does not assign any value to your variable. Trust me, Bill is right.

Subrata Banerjee Over a year ago

@Shadow let me try

Subrata Banerjee Over a year ago

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

Barmar Over a year ago

@SubrataBanerjee LAST_INSERT_ID() is only set for an ID assigned by auto-increment, not when you specify the value explicitly.

Uueerdo · Accepted Answer · 2017-11-30 21:20:31Z

0

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

answered Nov 30, 2017 at 21:20

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Collectives™ on Stack Overflow

INSERT query not running in MySQL stored procedure

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related