CODEWITHSUNDEEP
mysqlmysql-workbench
Nick Shaw
Nick Shaw

Reputation: 2113

MySQL stored procedure: OUT parameter not being set

I've got a stored procedure in MySQL that gets the next unique ID from a table, to use as an ID for 2 other tables (not the best way to do it, I'm sure, but I'm modifying someone else's code here). The procedure is as follows:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `GetNextID`( OUT id bigint )
BEGIN
  DECLARE uid VARCHAR(255);
  SET uid = uuid();
  INSERT INTO `ident_column_generator` (u) VALUES (uid);
  SELECT ID INTO id FROM `ident_column_generator` WHERE u = uid;
  DELETE FROM `ident_column_generator` WHERE u = uid;
END$$

When I call the procedure from MySQL Workbench:

CALL GetNextID( @id );
SELECT @id;

@id is NULL. I can't work out what's going wrong? Even if I run SET @id = 0; before calling the procedure, it ends up as NULL afterwards. If I call the functions within the procedure manually from MySQL Workbench, @id outputs fine, e.g.:

SET @uid = uuid();
INSERT INTO `ident_column_generator` (u) VALUES (@uid);
SELECT ID INTO @id FROM `ident_column_generator` WHERE u = @uid;
DELETE FROM `ident_column_generator` WHERE u = @uid;
SELECT @id;

This outputs @id as being a valid number.

Any ideas why id isn't being set properly?

Upvotes: 3

Views: 7334

Answers (2)

Ashish Sahu
Ashish Sahu

Reputation: 335

Thanks Nick, I also had same issue. The column name and variable name were same due to which we were getting the issue.

Upvotes: 0

Nick Shaw
Nick Shaw

Reputation: 2113

Typically, spent 3 hours on this, then JUST after I posted the question I find the problem. So, for future reference: It appears MySQL is case insensitive where variables are concerned. The ID column name and id variable apparently completely confused it.

I changed the procedure's input parameter name to retId and then it worked perfectly.

Upvotes: 3

Related Questions