MySQL Forums
Forum List  »  Stored Procedures

Re: SQL Query with variable name table in Stored Procedure using MySql 8 version
Posted by: Mark Sunderland
Date: April 09, 2021 10:13PM

Mysql version 8.0.12

sproc code complete without error

CREATE DEFINER=`root`@`%` PROCEDURE `SP`()
BEGIN

DECLARE tablename LONGTEXT;
DECLARE table_name LONGTEXT;
DECLARE tyear INT(4);

SET SESSION group_concat_max_len = 1000000;
SET GLOBAL connect_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;
SET GLOBAL wait_timeout = 28800;

SET tyear = YEAR(CURDATE());
SET @tbl = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H'),'_',tyear);

SET tablename = CONCAT('t_contents_',@tbl,'');
SET table_name = CONCAT('t_contents_q_,@tbl,'');

FLUSH HOSTS;

SET @sql = NULL;

SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
      ) INTO @sql
    FROM  t_contents_2021;		
					
FLUSH HOSTS;		
		
SET @sql = CONCAT('SELECT ', @sql,'
FROM
(
	SELECT
		tcs.rn,
		tcs.puni,
		tcs.pname,
		replace(fnStripTags(tcs.contents)," ","") contents 
	FROM
		`', table_name, '` AS q
		INNER JOIN ( SELECT puni, pname, contents, ROW_NUMBER() OVER ( PARTITION BY puni ORDER BY sID ) AS rn FROM `', tablename, '` ) AS tcs ON tcs.puni = q.puni
	) AS `', tablename, '` 
GROUP BY
	rn 
ORDER BY
	rn;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT @sql;		

FLUSH HOSTS;

END

sproc code complete with error

CREATE DEFINER=`root`@`%` PROCEDURE `SP`()
BEGIN

DECLARE tablename LONGTEXT;
DECLARE table_name LONGTEXT;
DECLARE tyear INT(4);

SET SESSION group_concat_max_len = 1000000;
SET GLOBAL connect_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;
SET GLOBAL wait_timeout = 28800;

SET tyear = YEAR(CURDATE());
SET @tbl = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H'),'_',tyear);

SET tablename = CONCAT('t_contents_',@tbl,'');
SET table_name = CONCAT('t_contents_q_,@tbl,'');

FLUSH HOSTS;

SET @sql = NULL;

SET @GetName = CONCAT('
SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
      ) INTO @sql
    FROM  `', tablename, '`;');		
		
SELECT @GetName;

PREPARE stmt FROM @GetName;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FLUSH HOSTS;		
		
SET @sql = CONCAT('SELECT ', @sql,'
FROM
(
	SELECT
		tcs.rn,
		tcs.puni,
		tcs.pname,
		replace(fnStripTags(tcs.contents)," ","") contents 
	FROM
		`', table_name, '` AS q
		INNER JOIN ( SELECT puni, pname, contents, ROW_NUMBER() OVER ( PARTITION BY puni ORDER BY sID ) AS rn FROM `', tablename, '` ) AS tcs ON tcs.puni = q.puni
	) AS `', tablename, '` 
GROUP BY
	rn 
ORDER BY
	rn;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT @sql;		

FLUSH HOSTS;

END

this part edited of sproc code this the problem when I try to using variable name table `', tablename, '` instead of `t_contents_2021`

SET @GetName = CONCAT('
SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')
      ) INTO @sql
    FROM  `', tablename, '`;');		
		
PREPARE stmt FROM @GetName;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.