mysql动态sql查询语句赋予变量

 

CREATE DEFINER=`dmladmin`@`%` PROCEDURE `sp_select_into_variable`()
    DETERMINISTIC
BEGIN
declare l_delete_date varchar(16);
declare l_state_date  varchar(16);
declare l_dutyno      int;
declare l_row_cnt int DEFAULT 0;
declare rn        int default 0;
declare i         int default 0;
DECLARE code      CHAR(5) DEFAULT '00000';
declare l_cnt     int default 0;

DECLARE exit handler FOR SQLEXCEPTION
BEGIN
     GET DIAGNOSTICS CONDITION 1
       code = RETURNED_SQLSTATE,@x2 = MESSAGE_TEXT;
     insert into tb_stat_error_log(dutydate,dutyno,error_msg) values(l_state_date,l_dutyno,@x2);
END; 

set l_dutyno=333;
set l_state_date=date_format(now(),'%Y%m%d');



set i=180;
while i<=250 DO
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,0,concat('开始查询表app_message_',i));
      /*set @strsql01 = CONCAT('select count(1) into @l_cnt from db_pushmsg.app_message_',i);*/
      set @strsql01 = CONCAT('select count(1) into @l_cnt from db_test.tb_test',i,' where create_time >= str_to_date(''2025-09-08 00:00:00'', ''%Y-%m-%d %H:%i:%s'') and create_time < str_to_date(''2025-09-10 00:00:00'', ''%Y-%m-%d %H:%i:%s'') and  remakr like  ''abc%''');
      /*select @strsql01;*/
      PREPARE stmt01 FROM @strsql01;
      execute stmt01;
      deallocate prepare stmt01;
      set l_cnt = @l_cnt;
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,1,concat('结束查询表app_message_',i,'记录数:',l_cnt));
set i = i +1;

end while;

END