Mysql dynamically builds a query string in a stored procedure based on logic

Question

The goal is to change the query string in a Mysql stored procedure based on input variables.

Something like that:

CREATE DEFINER=`root`@`localhost` PROCEDURE `func`(type VARCHAR(15)) BEGIN SET @type = type; -- Check for the sort parameter if @type="asc" THEN SET @sort = " order by name asc"; elseif @type="desc" THEN SET @sort = " order by name desc"; else SET @sort =""; end if; SELECT id, name from table @sort; END 

Answer 1

The solution is to use execute and concat:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(input VARCHAR(15)) BEGIN SET @input = input; if @input="asc" then SET @sort = " order by ActivityLogKey asc"; elseif @input = "desc" then SET @sort = " order by ActivityLogKey desc"; else SET @sort =""; end if; SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; END