MySQL Forums
Forum List  »  Stored Procedures

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

Hi,

This is my SQL query that working correctly and it's stored an a database MySql version 8.0.17

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

I've tried using this SQL query on an a Stored Procedure on this mode

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

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

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;

But I've error on this line

CONCAT ( "max(IF(`pname` = '",pname,'\',contents,NULL)) AS `', pname, '`')

Any help would greatly appreciate... Thank you.

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.