Robin Persson Robin Persson - 7 months ago 21
SQL Question

MySQL stored procedure returns wrong values

Im new to MySQL stored procedures and I was following some tutorial on how to use them, but I ran into an interesting thing with the following:

DELIMITER $$
CREATE DEFINER=`user`@`%` PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
SELECT COUNT(*)
INTO test
FROM myTable
WHERE id = ID;
END$$
DELIMITER ;


I run it with this:

CALL CalculateScores(252, @test);


and then just:

SELECT @test;


The strange thing is that
@test
returns the total row count of the entire table not just for the
id
I sent as a parameter.

What am I missing here? The tutorial never mention this, and I can't find an answer to why this is happening, I might suck at searching..

Answer

It looks like MySQL cannot differentiate between id and ID:

SELECT COUNT(*)
INTO test
FROM myTable
WHERE id = ID;

And it treats it like 1 = 1 which is always true (if column is not nullable).


You could add alias to indicate that id is column and not parameter.

CREATE PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
    SELECT COUNT(*)
    INTO test
    FROM myTable t
    WHERE t.id = ID;
END

SqlFiddleDemo