喜欢不在存储过程中工作

Question

I have written stored procedure using like operator, but i am getting error of mysql. Here is my query:

CREATE DEFINER=`ecp`@`localhost` PROCEDURE `sp_getTier4CatBanner`(pSiteid int,pTier4 varchar(100))
BEGIN
    SET @SS = CONCAT("SELECT *  FROM `categorybanners` WHERE TIERCODES LIKE %",pTier4,"%"," and SITEID=",pSiteid," and CATLEVEL='tire4'");
    PREPARE STMT FROM @SS;
END

When i print the query i get the following result:

SELECT *  FROM `categorybanners` WHERE TIERCODES LIKE %t2_05% and SITEID=46 and  CATLEVEL='tire4'

The main cause of error missing single quotes from %t2_05%. Please let me know how to put single quotes, to make query look like this:

SELECT *  FROM `categorybanners` WHERE TIERCODES LIKE '%t2_05%' and SITEID=46 and  CATLEVEL='tire4'

Answer 1

Just put the single quotes in front of the first % and after the last %:

SET @SS = CONCAT("SELECT *  FROM `categorybanners` WHERE TIERCODES LIKE '%",pTier4,"%'"," and SITEID=",pSiteid," and CATLEVEL='tire4'");

So that your statement becomes:

SELECT * FROM categorybanners WHERE TIERCODES LIKE '%t2_05%' and SITEID=46 and CATLEVEL='tire4'