MySQL returning two different results from query vs stored procedure

I’m baffled as to why MySQL Workbench returns 7 (the correct answer) for this query

SELECT ScheduleLocationEventColorID 
FROM schedulelocationeventcolor 
WHERE ScheduleID = 1 AND LocationID = 1;

but returns 1 (incorrect) for the equivalent in a stored procedure.

CREATE PROCEDURE `test`(
    IN locationID INT,
    IN scheduleID INT
)
BEGIN
    DECLARE slecID INT;

    SELECT ScheduleLocationEventColorID 
    INTO slecID 
    FROM schedulelocationeventcolor 
    WHERE ScheduleID = scheduleID AND LocationID = locationID
    LIMIT 1;
    
    SELECT slecID;
END

-- calling the stored procedure here: 
CALL `test`(1, 1);

I tried changing the inputs in the stored procedure call, but I cannot figure out for the life of me why I keep getting a 1 when the result should be a 7.

>Solution :

You should name your procedure parameters something different from your column names. Column names are case-insensitive.

Right now your procedure has a query with this condition:

WHERE ScheduleID = ScheduleID AND LocationID = LocationID

Which evaluates as true AND true because comparing a column to itself is bound to be true (unless the column is NULL).

So with the LIMIT 1, the query returns the first row where both columns are not NULL.

Leave a Reply