Re: problem in stored procedure with dynamic tables with mysql

On 6/25/2018 2:30 AM, shwetha.rohith28_at_gmail.com wrote:
> Hi All,
>
> I have created the stored procedure which is using dynamic tables.
>
> here i wanted to assign output from select query to a variable. But its not working. kindly help to get the solution for the issue.
>
> stored procedure is not working if i store count value to variable like below
> SET _at_sql_text = concat('select count(*) into @v_count from ',_at_tname);
>
>
> Below is my Stored procedure
> BEGIN
> DECLARE v_tblname varchar(100) DEFAULT "";
> DECLARE v_smodtblname varchar(100) DEFAULT "";
> DECLARE v_finished INTEGER DEFAULT 0;
> DECLARE v_count INTEGER DEFAULT 0;
>
>
> DECLARE Cur_tablename CURSOR
> FOR
> SELECT table_name FROM information_schema.tables where table_schema='SMoDSharedComm_16000' and table_name LIKE 'sys_email%';
> -- declare NOT FOUND handler
> DECLARE CONTINUE HANDLER
> FOR NOT FOUND SET v_finished = 1;
>
> insert into Task_EmailSent(Inc_Sys_ID,`Number`) select `sys_id`,`Number` from `SMoDSharedComm_16000`.`task` `A`
> where `A`.`sys_class_name`='incident' AND NOT
> EXISTS (SELECT 1 FROM Task_EmailSent WHERE Inc_Sys_ID=A.sys_id);
>
>
> OPEN Cur_tablename;
> get_tblname: LOOP
> FETCH Cur_tablename INTO v_tblname;
>
> IF v_finished = 1 THEN
> LEAVE get_tblname;
> END IF;
> select 'before qry';
> set v_smodtblname:=concat('SMoDSharedComm_16000.',v_tblname);
>
> select v_smodtblname;
>
> SET _at_tname:=v_smodtblname;
>
> SET _at_sql_text = concat('select count(*) into @v_count from ',_at_tname);
>
> /*SET _at_sql_text = concat('select count(1) from ',_at_tname,'`A` inner join Task_EmailSent `B`
> on `A`.instance= `B`.Inc_Sys_ID');
> */
>
> PREPARE stmt FROM _at_sql_text;
> EXECUTE stmt;
>
> DEALLOCATE PREPARE stmt;
>
> select v_count;
>
>
> END LOOP get_tblname;
> CLOSE Cur_tablename;
>
>
> END
>

Did you check for errors? It looks like you are missing a space before `A`.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================