CODEWITHSUNDEEP
mysqlsqlstored-procedures
Robin Persson
Robin Persson

Reputation: 53

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..

Upvotes: 5

Views: 1048

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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

db<>fiddle demo

Upvotes: 6

Related Questions