CODEWITHSUNDEEP
mysqlsqlstored-procedures
GvanJoic
GvanJoic

Reputation: 213

Error in SET command in a MySQL Procedure

I wish to retrieve all distinct values of a column (Task in this case) from a table (dataset in this case). The task values will go as column name for the table that I intend to create using the procedure (albeit not defined currently but only printed). But it gives an error at the line 

SET qry = CONCAT("CREATE TABLE ",tblname," ( TEAM varchar(20) PRIMARY KEY ," );

saying end was missing. When I put end it says semicolon is missing and vice-versa.

DELIMITER //

CREATE PROCEDURE `createTable` (IN tblName varchar(20))
    BEGIN
        DECLARE task varchar(20) DEFAULT "";
    DECLARE exit_loop BOOLEAN; 


    SET qry = CONCAT("CREATE TABLE ",tblname," ( TEAM varchar(20) PRIMARY KEY ," );

    DECLARE task_curs CURSOR FOR SELECT DISTINCT Task FROM dataset;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

    OPEN task_curs;

    my_loop: loop

        FETCH task_curs into task;
        qry=CONCAT(qry,task, ' INT  DEFAULT 0, ');

        IF exit_loop THEN
            CLOSE task_curs;
            LEAVE my_loop;
        END IF;
   END LOOP my_loop;

   qry=TRIM(TRAILING ',' FROM qry);
   qry=CONCAT(qry, ');');
   SELECT qry;
END; //
DELIMITER ;

What might be possibly wrong with the syntax ? Thanks!

Upvotes: 0

Views: 108

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Try:

DELIMITER //

CREATE PROCEDURE `createTable` (IN tblName varchar(20))
    BEGIN
        DECLARE task varchar(20) DEFAULT "";
    DECLARE exit_loop BOOLEAN; 
    DECLARE qry VARCHAR(500);

    -- SET qry = CONCAT("CREATE TABLE ",tblname," ( TEAM varchar(20) PRIMARY KEY ," );

    DECLARE task_curs CURSOR FOR SELECT DISTINCT Task FROM dataset;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

    SET qry = CONCAT("CREATE TABLE ",tblname," ( TEAM varchar(20) PRIMARY KEY ," );

    OPEN task_curs;

    my_loop: loop

        FETCH task_curs into task;
        -- qry=CONCAT(qry,task, ' INT  DEFAULT 0, ');
        -- SET qry=CONCAT(qry,task, ' INT  DEFAULT 0, ');

        IF exit_loop THEN
            CLOSE task_curs;
            LEAVE my_loop;
        END IF;

        SET qry=CONCAT(qry,task, ' INT  DEFAULT 0, ');

   END LOOP my_loop;

   -- qry=TRIM(TRAILING ',' FROM qry);
   SET qry=TRIM(TRAILING ',' FROM qry);

   -- qry=CONCAT(qry, ');');
   SET qry=CONCAT(qry, ');');

   SELECT qry;
END; //
DELIMITER ;

Upvotes: 1

Related Questions