mysql 提高一 动态sql 传变量

1.需求

DELIMITER $$

ALTER DEFINER=`root`@`%` EVENT `AutoFinishNightCase` ON SCHEDULE EVERY 1 SECOND STARTS '2018-09-07 08:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN 
    DECLARE goodIds VARCHAR(255) DEFAULT '';     
    SET @goodIds:=(SELECT TRIM(EnumValue) FROM dictonary WHERE DictionaryName='NightRegisterGoodId');
 UPDATE his_caseinfo  AS caseinfo  INNER JOIN 
 (SELECT posregister.`Id`   FROM posregister  WHERE posregister.`GoodId`IN (CONCAT(@goodIds)))p 
 SET caseinfo.`CaseStatus`=4  
 WHERE caseinfo.RegistrationId=p.id   AND (caseinfo.casestatus=0 OR caseinfo.casestatus=2) AND `CreateTime`<DATE_FORMAT(NOW(),'%Y-%m-%d 10:00:00');
END$$

DELIMITER ;

2.问题点，以上代码：

posregister.`GoodId`IN (CONCAT(@goodIds))，这种写法不对

3.解决方式，动态执行sql，相当于sqlserver里面的存储过程exec('select *from his_caseinfo')

PREPARE  myselect FROM CONCAT('SELECT * FROM his_caseinfo');
 EXECUTE myselect;

 

转载于:https://www.cnblogs.com/ligenyun/p/9603197.html