1

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

Cœur
  • 34,719
  • 24
  • 185
  • 251
user1282637
  • 1,777
  • 4
  • 25
  • 52

1 Answers1

4

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%'.

user3413723
  • 9,562
  • 5
  • 49
  • 62
  • When I change it to your recommendation I get "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ gameName + '%'; END' at line 5" – user1282637 Apr 12 '15 at 20:30
  • Hm, not sure why. Maybe try using `CONCAT('%',gameName,'%')` instead of `'%' + gameName + '%';` – user3413723 Apr 12 '15 at 20:59