mysql存储过程中写逻辑,MySQL基于逻辑在存储过程中动态构建查询字符串

The objective is to alter a query string within a Mysql stored procedure based on input variables.

Something like this:

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

解决方案

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