Nick Shaw Nick Shaw - 7 months ago 9
SQL Question

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?

Answer

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.