My SQL Store procedure print dynamic QUERY

Question

I am very to the new MYSQL and creating a store procedure using dynamic query which is containing the steps as

CREATE TEMPORARY TABLE(THE NAME OF THE TEMPORARY TABLE SHOULD BE UNIQUE)

USING THE TEMPORARY TABLE INSERTING THE DATA INTO ANOTHER TABLE

Below is the code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test` () 
BEGIN

SET @TABLE_NAME:=concat('log_summary_',replace(uuid(),'-',''));
SET @in_seed_url_id='2f647ede-652b-4953-812c-6ba4e78f96f9';
SET @sql:='CREATE TABLE IF NOT EXISTS '+@TABLE_NAME+' (INDEX summary_index(system_url_id, system_id,page_count,url_id,name,type)) AS  
SELECT 
    md5(concat(name,system_url_id,pixel_id,type,category))as id
    ,md5(concat(name,system_url_id,pixel_id,type,category,count(DISTINCT page_id)) as char(1000))as migrate_id
    ,count(DISTINCT page_id) as page_count
    ,name
    ,system_url_id
    ,system_id
    ,type
    ,category
    ,url_id
FROM log 
WHERE seed_url_id='2f647ede-652b-4953-812c-6ba4e78f96f9'
GROUP BY name
    ,system_url_id
    ,pixel_id
    ,type
    ,category
    ,url_id; 

INSERT INTO Aggregate_log
SELECT DISTINCT 
    ,id
    ,system_id
    ,name
    ,type
    ,category
    FROM +@TABLE_NAME;

';

PREPARE stmt3 FROM @sql;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

END$$
DELIMITER 

When I am running the above store procedure in mysql then it is giving me the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1

When I have printed the @sql it is showing the output as 2.But my log is containing more than 1000 rows .

actually, i need to create a temporary table with unique names,that's why using the dynamic query .Any alternative to create the temporary table with unique name and to use it further in procedure without using dynamic query ?

Can anyone help me ,what i am doing wrong?

Answer 1

Use cast to concatenate , use char(32) , ascii single quote , when embedding strings and submit 1 statement at a time eg

drop procedure if exists p;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test` () 
BEGIN

SET @TABLE_NAME:=concat('log_summary_',replace(uuid(),'-',''));
SET @in_seed_url_id='2f647ede-652b-4953-812c-6ba4e78f96f9';
SET @sql:=
concat(
'CREATE TABLE IF NOT EXISTS ',@TABLE_NAME,' (INDEX summary_index(system_url_id, system_id,page_count,url_id,name,type)) AS  
SELECT 
    md5(concat(name,system_url_id,pixel_id,type,category))as id
    ,md5(concat(name,system_url_id,pixel_id,type,category,count(DISTINCT page_id)) as char(1000))as migrate_id
    ,count(DISTINCT page_id) as page_count
    ,name
    ,system_url_id
    ,system_id
    ,type
    ,category
    ,url_id
FROM log 
WHERE seed_url_id=', char(32),'2f647ede-652b-4953-812c-6ba4e78f96f9',char(32),
'GROUP BY name
    ,system_url_id
    ,pixel_id
    ,type
    ,category
    ,url_id; 
'
);

#PREPARE stmt3 FROM @sql;
#EXECUTE stmt3;
#DEALLOCATE PREPARE stmt3;
/*
INSERT INTO Aggregate_log
SELECT DISTINCT 
    ,id
    ,system_id
    ,name
    ,type
    ,category
    FROM +@TABLE_NAME;

';
*/
#PREPARE stmt3 FROM @sql;
#EXECUTE stmt3;
#DEALLOCATE PREPARE stmt3;

END$$
DELIMITER ;

I'll leave you to sort out the insert.