Nick Shaw Nick Shaw - 2 years ago 111
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:

CREATE DEFINER=`root`@`%` PROCEDURE `GetNextID`( OUT id bigint )
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;

When I call the procedure from MySQL Workbench:

CALL GetNextID( @id );

@id is
. 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,
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;

This outputs @id as being a valid number.

Any ideas why
isn't being set properly?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download