CODEWITHSUNDEEP
mysqlsqlstored-procedures
Andy Braham
Andy Braham

Reputation: 10171

MySQL Stored Procedure returning more than expected

I am starting to play with stored procedures but am having some problems, I am getting more rows than I expect. I have a table with the following data:

----------------------
| user_id | username |
----------------------
|    1    |   fred   |
----------------------
----------------------
|    2    |   andy   |
----------------------

And have a procedure as follows:

CREATE PROCEDURE `VerifyUser`(userId INT, userName CHAR(40))
BEGIN
    SELECT * FROM users WHERE user_id = userId AND username = userName;
END

The problem is I am getting a result no matter what I enter for "userName", I seem to be getting the record for user_id=2:

CALL VerifyUser(2, 'dontWork');

----------------------
| user_id | username |
----------------------
|    2    |   andy   |
----------------------

I can get it to work if I only have userId but when I add the second parameter I get a result no matter what. WHAT AM I DOING WRONG? I am assuming it has something to do with the string parameter.

Thanks!

Upvotes: 0

Views: 112

Answers (1)

juergen d
juergen d

Reputation: 204904

The problem is that your column name and your parameter name are the same (case-insensitive). Use a different name for the parameter and you will be fine.

Otherwise the DB thinks you mean 

username_column = username_column

which is always true.

Upvotes: 2

Related Questions