0

I want to create temp table dynamically. I am passing column name as a parameter to stored procedure.

Example:

DELIMITER $$
create procedure sptemp(IN nm varchar(50))
BEGIN

SET @q = CONCAT('CREATE TEMPORARY TABLE temptable    // Error occuring here @q 
             SELECT DISTINCT ', nm ,' AS Col FROM table1');
PREPARE d FROM @q;
EXECUTE d;
DEALLOCATE PREPARE d;
END$$
DELIMITER ;

Error: Syntax error, unexpected '@', expecting ':'

Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84

1 Answers1

1
SET @q = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS temptable (INDEX(Col)) ENGINE=MyISAM 
AS (SELECT DISTINCT ', nm ,' AS Col FROM table1)');

As a bonus - index of your Col column.

Jevgenijs Vaikulis
  • 676
  • 4
  • 7