MySQL Stored Procedure not searching wildcard

Question

I have a stored procedure in MySQL that is like:

CREATE PROCEDURE `sample_search`(
IN upperrange decimal(2,0),
IN lowerrange decimal(2,0),
IN name varchar(31),
IN age varchar(20)
)
BEGIN
Select 
    name,
    age,
    rank,
FROM sampledatabase
WHERE rank BETWEEN lowerrange AND upperrange
    AND name LIKE name
    AND age LIKE age;
END

When I call the procedure like so:

Call sample_search(20, 10, '%bob%', '%teen%');

It returns the search for the range but will not search based on the wildcards.

|name    |age    |rank    |
|%bob%   |%teen% |15      |
|%bob%   |%teen% |16      |

It just fills in what I meant to search. If I change the procedure to:

CREATE PROCEDURE `sample_search`(
IN upperrange decimal(2,0),
IN lowerrange decimal(2,0),
IN name varchar(31),
IN age varchar(20)
)
BEGIN
Select 
    name,
    age,
    rank,
FROM sampledatabase
WHERE rank BETWEEN lowerrange AND upperrange
    AND name LIKE CONCAT('%', name, '%')
    AND age LIKE CONCAT('%', age, '%');
END

I get the same result. Any ideas what I'm doing wrong?

Answer 1

The arguments of the procedure have the same names as the table columns. This is ambiguous; in the query, MySQL understands the name of the parameter as a column name - a condition like name = name is always true (unless name is null), so these filters become no-ops.

Use non-ambiguous names for the procedure arguments:

CREATE PROCEDURE `sample_search`(
    IN p_upperrange decimal(2,0),
    IN p_lowerrange decimal(2,0),
    IN p_name varchar(31),
    IN p_age varchar(20)
)
BEGIN
    Select 
        name,
        age,
        rank,
    FROM sampledatabase
    WHERE rank BETWEEN p_lowerrange AND p_upperrange
        AND name LIKE p_name
        AND age LIKE p_age;
END