MySql stored procedure with limit parameters gives err.no: 1064 when we use the CONCAT operator

Question

We have a long sql procedure that takes limit and amount (limitCount) parameters. So, we use the concat operator to combine multiple queries. Calling this procedure gives err.no 1064 when we try to start it.

EDIT: based on the comment, I add all the code.

CREATE PROCEDURE getProfileTasks (IN p_id1 INT, IN p_id2 INT , IN limitStart INT, IN limitCount INT) BEGIN SET @SQL = CONCAT(' SELECT P.access_type INTO @privacy FROM Profile P WHERE P.profile_id = ' , p_id2 , '; IF( ' , p_id1, ' = ' ,p_id2 , ') THEN SELECT T.task_id, T.name, D.add_time, D.location, DATE_FORMAT(D.date1, "%d/%m/%y") as `date1`, D.time3, D.state, TIME_FORMAT(D.time1, "%H:%i")as `time1`, D.does_id, IFNULL(L.Like, 0) AS `LikeCount` , IFNULL(C.CommentCount,0) AS `CommentCount` FROM Task T INNER JOIN Does D on D.task_id = T.task_id INNER JOIN Profile P on P.profile_id = D.profile_id LEFT OUTER JOIN ( SELECT D.does_id, COUNT(L.profile_id) as `Like` FROM `Likes` L INNER JOIN Does D on D.does_id = L.does_id INNER JOIN Profile P on P.profile_id = D.profile_id WHERE P.profile_id = ' , p_id2 , ' GROUP BY does_id) L on L.does_id = D.does_id LEFT OUTER JOIN( SELECT D.does_id, COUNT(C.content) AS `CommentCount` FROM Comment C INNER JOIN Does D on D.does_id = C.does_id GROUP BY (D.does_id)) C ON C.does_id = D.does_id WHERE P.profile_id= ' , p_id2, ' ORDER BY D.add_time DESC LIMIT ' , limitStart , ', ' , limitCount, '; ELSE IF (@privacy = 0) THEN SELECT T.task_id, T.name, D.add_time, D.location, DATE_FORMAT(D.date1, "%d/%m/%y") as `date1`, D.time3, D.state, TIME_FORMAT(D.time1, "%H:%i")as `time1`, D.does_id, IFNULL(L.Like,0) AS `LikeCount`, IFNULL(C.CommentCount,0) AS `CommentCount` FROM Task T INNER JOIN Does D on D.task_id = T.task_id INNER JOIN Profile P on P.profile_id = D.profile_id LEFT OUTER JOIN ( SELECT D.does_id, COUNT(L.profile_id) as `Like` FROM `Likes` L INNER JOIN Does D on D.does_id = L.does_id INNER JOIN Profile P on P.profile_id = D.profile_id WHERE P.profile_id = ' , p_id2 , ' GROUP BY does_id) L on L.does_id = D.does_id LEFT OUTER JOIN( SELECT D.does_id, COUNT(C.content) AS `CommentCount` FROM Comment C INNER JOIN Does D on D.does_id = C.does_id GROUP BY (D.does_id) )C ON C.does_id = D.does_id WHERE P.profile_id= ' ,p_id2, ' ORDER BY D.add_time DESC LIMIT ' , limitStart , ', ' , limitCount, '; ELSE IF EXISTS ( SELECT * FROM Follows F INNER JOIN Profile P on F.follower_id = P.profile_id INNER JOIN Profile P2 on F.following_id = P2.profile_id WHERE (P.profile_id = ' , p_id1, ' AND P2.profile_id = ' , p_id2 , ')) THEN SELECT T.task_id, T.name, D.add_time, D.location, DATE_FORMAT(D.date1, "%d/%m/%y") as `date1`, D.time3, D.state, TIME_FORMAT(D.time1, "%H:%i")as `time1`, D.does_id, IFNULL(L.Like,0) AS `LikeCount`, IFNULL(C.CommentCount,0) AS `CommentCount` FROM Task T INNER JOIN Does D on D.task_id = T.task_id INNER JOIN Profile P on P.profile_id = D.profile_id LEFT OUTER JOIN ( SELECT D.does_id, COUNT(L.profile_id) as `Like` FROM `Likes` L INNER JOIN Does D on D.does_id = L.does_id INNER JOIN Profile P on P.profile_id = D.profile_id WHERE P.profile_id = ' , p_id2 , ' GROUP BY does_id) L on L.does_id = D.does_id LEFT OUTER JOIN( SELECT D.does_id, COUNT(C.content) AS `CommentCount` FROM Comment C INNER JOIN Does D on D.does_id = C.does_id GROUP BY (D.does_id) )C ON C.does_id = D.does_id WHERE P.profile_id= ' , p_id2 , ' ORDER BY D.add_time DESC LIMIT ' , limitStart , ', ' , limitCount, '; END IF; END IF; END IF; ' ); PREPARE query FROM @SQL; EXECUTE query; DEALLOCATE PREPARE query; END 

Does anyone have an idea why we get this error?

 ERROR : #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 'IF( 18 = 18) THEN SELECT T.task_id, T.name, D.add_time, D.location, DATE_' at line 2 

PS: DELIMITER is set to //

Answer 1

Since the way to create a request in a stored procedure was not really server friendly (and was not friendly to other developers), I reformatted the stored procedure. Then I also noted that this is actually the same query that is executed in three cases, so there is a lot of duplication.

 CREATE PROCEDURE getProfileTasks (IN p_id1 INT, IN p_id2 INT , IN limitStart INT, IN limitCount INT) BEGIN DECLARE privacy INT; DECLARE is_following INT; SELECT P.access_type INTO privacy FROM Profile P WHERE P.profile_id = p_id2; SELECT COUNT(*) INTO is_following FROM Follows F INNER JOIN Profile P on F.follower_id = P.profile_id INNER JOIN Profile P2 on F.following_id = P2.profile_id WHERE (P.profile_id = p_id1 AND P2.profile_id = p_id2) LIMIT 1; IF ( (p_id1 = p_id2) OR (privacy = 0) OR (is_following > 0) ) THEN -- Using CONCAT and prepared statement because LIMIT won't work otherwise @SQL = CONCAT(' SELECT T.task_id , T.name , D.add_time , D.location , DATE_FORMAT(D.date1, "%d/%m/%y") AS `date1` , D.time3 , D.state , TIME_FORMAT(D.time1, "%H:%i") AS `time1` , D.does_id , IFNULL(L.`Like`, 0) AS `LikeCount` , IFNULL(C.CommentCount,0) AS `CommentCount` FROM Task T INNER JOIN Does D on D.task_id = T.task_id INNER JOIN Profile P on P.profile_id = D.profile_id LEFT OUTER JOIN ( SELECT D.does_id, COUNT(L.profile_id) AS `Like` FROM `Likes` L INNER JOIN Does D on D.does_id = L.does_id INNER JOIN Profile P on P.profile_id = D.profile_id WHERE P.profile_id = @p_id2 GROUP BY does_id ) L on L.does_id = D.does_id LEFT OUTER JOIN ( SELECT D.does_id, COUNT(C.content) AS `CommentCount` FROM Comment C INNER JOIN Does D on D.does_id = C.does_id GROUP BY (D.does_id) ) C ON C.does_id = D.does_id WHERE P.profile_id= @p_id2 ORDER BY D.add_time DESC LIMIT ', limitStart, ', ' , limitCount, ';'); SET @p_id2 = p_id2; PREPARE query FROM @SQL; EXECUTE query USING @p_id2; DEALLOCATE PREPARE query; END IF; END 

And I also added backlinks around Like to IFNULL(L.`Like`, 0) AS `LikeCount` , because this is a potential problem mentioned in the comments to the question.