Search code examples
mysqlsqlsql-like

Using SQL LIKE operator like a Stored Procedure paramater

I'm trying to use LIKE %...% in a stored procedure but I'm not sure how to use the incoming variable in the operator. For example, I'm doing this:

DELIMITER //
CREATE PROCEDURE GetGameByName(IN gameName varchar(255))
 BEGIN
 SELECT * 
 FROM game
 WHERE gameTitle LIKE '% + gameName + %';
 END //
DELIMITER ;

but when I call it like this 

CALL GetGameByName('Creed');

It is returning nothing (I DO have a game with gameTitle "Assassin's Creed"

Any ideas what I'm doing wrong? Thanks

Solution

  • UPDATED due to issue in comment - the solution is now WHERE gameTitle LIKE CONCAT('%',gameName,'%')

    ===== Previous Answer =====

    It looks to me like you forgot quotation marks. Instead of WHERE gameTitle LIKE '% + gameName + %';, you should probably do WHERE gameTitle LIKE '%' + gameName + '%';

    The way you have it set up, you are feeding the incorrect generic text '%gameTitle%' into the query, instead of what you really want, which is something like: '%Creed%'.