CODEWITHSUNDEEP
mysqlsql
user12816917
user12816917

Reputation: 115

MySQL stored procedure returns all rows, ignoring the WHERE clause

I'm a bit confused as I couldn't find the reason why my simple stored procedure isn't working as intended. Here is the stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetEntriesTest`(
    in centerid int
)
BEGIN
    SELECT * 
    FROM `Entries` 
    WHERE `CenterId` = centerid;
END

The SP is supposed to select rows from Entries table where the given centerid parameter matches the CenterId column, but it keeps returning all rows in the table, even where CenterId does not match centerid. I tried without enclosing the table and column names without ``, but same result. I really couldn't find what I am doing wrong, if anybody could shed some light I would be much grateful!

Upvotes: 1

Views: 918

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

It is working just fine. You think that one of those centerids magically refers to a column and the other to a parameter. Nope. They both refer to the column in the table.

So, give your variables distinctive names:

tended. Here is the stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetEntriesTest` (
    in in_centerid int
)
BEGIN
    SELECT * 
    FROM Entries e
    WHERE e.CenterId = in_centerid;
END;

Upvotes: 3

Related Questions