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