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;

 

 
posted on 2018-09-07 10:47  biind  阅读(462)  评论(0编辑  收藏  举报