mySQL stored procedure where clause problem

Question

Hi!

I've got a mySql stored procedure that looks like this--

delimiter |
create procedure GetEmployeeById(in ID varchar(45))
begin
  select id,
      firstName,
      lastName,
      phone,
      address1,
      address2,
      city,
      state,
      zip,
      username,
      password,
      emptypeid
  from myschema.tblemployees t
  where
      t.id=ID limit 1;
end |
delimiter;

If i don't have the limit 1 in place, it always returns all of the rows in the table--with each record's id value set to the ID parameter. Why can't i just use where id=ID, and why does it return all of the records when i do? What are the implications of me using limit 1? Why am i programming on a saturday night?

Answer 1

Because, it's comparing t.id with itself, which will always be true. Call your formal parameter something else.

Answer 2

Column names in MySQL are not case-sensitive. The id column in your query hides the parameter named ID, so your where clause is really using two different expressions to refer to the same column. And of course a column's value is always equal to itself, so all the records match. Use a different name for the input parameter.