I just spent an HOUR trying to find out why a stored procedure was returning NULL no matter what i did!
Here is the procedure i was trying to create:
DELIMITER // CREATE PROCEDURE get_last_student_id(OUT last_id INT) BEGIN SELECT IF(id,id,0) INTO @last_id FROM students ORDER BY id DESC LIMIT 1; END; // DELIMITER ;
Simple right? Can you spot the mistake?
No? Well don’t worry, i didn´t either.
You see, the problem is with MySQL/MariaDB user defined variables. This is how you create and use variables in MySQL/MariaDB (note: variables don´t need to be defined before use):
-- Create @counter variable and set its value to 0 SET @counter := 0; -- Increment the @counter value by one SET @counter := @counter + 1; -- Show the @counter value SELECT @counter; -- Create @aux variable and set its value to 200 SET @aux = 200; -- Set @counter to 200 (@aux value) SELECT @aux INTO @counter; -- Prints: 200 SELECT @counter;
Can you see the mistake now? Look at all those pretty “@” everywhere!
They are infact used everywhere EXCEPT FOR stored procedure variables! So here is the mistake:
SELECT IF(id,id,0) INTO @last_id FROM students ORDER BY id DESC LIMIT 1;
@last_id should be just
last_id because, in reality,
last_id is a stored procedure parameter and not a real variable and parameters don’t have the “@” prefix… but wait! What´s this?
Here is how you call a stored procedure:
CALL get_last_student_id(@student_id); SELECT @student_id;
Wait, what? WTF? I’m passing a VARIABLE that turns into a stored procedure PARAMETER and then turns again into a VARIABLE when selecting it? Wut?
I guess consistency just flew out the window…