I have the following logic in a mysql stored proc:
SET @UserId = (
where ticketid = ticketID
where ticketid = ticketID;
Seems like the predicate in the WHERE clause
ticketid = ticketID
would evaluate to TRUE for all non-null values of ticketid.
Essentially equivalent to specifying
ticketid IS NOT NULL
ticketid is the PRIMARY KEY, then every row in the table would satisfy that condition.
I'm just guessing here. I suspect you have declared a variable named
ticketID and are expecting MySQL to differentiate between
ticketid being a reference to a column, and
ticketID being a reference to a variable. But that doesn't happen. If there is a procedure variable named
ticketID (that's declared in scope of a SQL statement), MySQL takes a reference to
ticketid as a reference to the variable. So, again, every row in the table will be returned if
ticketid variable has a non-null value.
From the MySQL Reference Manual:
A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets a reference to that name as the name of a variable.
If that's the issue (again, I'm just guessing there) a workaround is to qualify references to the columns with the table name or table alias.
A lot of developers also name their variables with a prefix, or some other convention, which makes it easier for the reader to distinguish variable names from column names, by reducing ambiguity.
DECLARE lv_ticketid INT; SET @UserId = ( SELECT t.userid FROM tickets t WHERE t.ticketid = lv_ticketid );
And where we know that only one row will be returned, we can also add a LIMIT 1 to the query, depending on whether we want an error thrown when there is more than one row returned.
SET @UserId = ( SELECT t.userid FROM tickets t WHERE t.ticketid = lv_ticketid ORDER BY t.userid ASC LIMIT 1 );