CODEWITHSUNDEEP
mysqlloopsvariablesstored-procedurescursor
Kausty
Kausty

Reputation: 859

Issue with substituting variable and cursor in stored procedure

So I know that in order to substitute a cursor in select statement we need to define it before the prepare statement. I can substitute variable there but I have an issue with same for cursors.

For example, the below statement should correctly substitute the variable values for table and should create a table as trial_data.

SET @user := 'trial';
SET @group := 'data';
SET @table := CONCAT(@user, '_', @group);
SET @stat = CONCAT('CREATE TABLE ',@table,' as SELECT @user, @group;');
PREPARE stmt from @stat;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

However, when I try to apply the same logic in a stored procedure with cursor, I run into an error as below -

01:13:52    call cursor_ROWPERROW() Error Code: 1064. 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 'NULL' at line 1    0.000 sec

In this scenario, I am trying to create tables dynamically by substituting the values of variable and cursor. Here I am trying to dynamically create PD_yyyyy_xxxxx tables based on the same columns from another table which I identify from the information schema.

DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER $$
CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
    DECLARE cursor_COL VARCHAR(50);
    DECLARE cursor_TAB VARCHAR(50);
    DECLARE tab VARCHAR(100);
    DECLARE col1 VARCHAR(50);
    DECLARE col2 VARCHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE cursor_i CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='xxxxx' AND TABLE_NAME='yyyyy' AND COLUMN_NAME = 'zzzzz';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cursor_i;
    read_loop: LOOP
        FETCH cursor_i INTO cursor_COL, cursor_TAB;
        SET tab := CONCAT('PD', '_', @cursor_TAB, '_' , @cursor_COL);
        SET col1 := CONCAT(@cursor_COL);
        SET col2 := CONCAT(@cursor_COL, '_', 'value');
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @dyn_sql := CONCAT('CREATE TABLE ',@tab,' AS SELECT Codevalue as ',@col1,', valueLabel as ',@col2,' from valuesets14 where varName = cursor_COL and TableName = cursor_TAB;');
        PREPARE stmt_dyn_view FROM @dyn_sql;
        EXECUTE stmt_dyn_view;
        DEALLOCATE PREPARE stmt_dyn_view;
    END LOOP;
    CLOSE cursor_i;
END;
$$

DELIMITER ;

Although, I replace the entire SET @dyn_sql block with a select statement like below, it runs fine and gives me the exact dataset I want to create multiple tables for. Note that none of the values in the table I am referencing is NULL, so it seems that I am missing something with cursor substitution.

SELECT Codevalue as ',@col1,', valueLabel as ',@col2,' from valuesets14 where varName = cursor_COL and TableName = cursor_TAB;

Can anyone please help me figure this one out?

Upvotes: 0

Views: 274

Answers (2)

Kausty
Kausty

Reputation: 859

Thank you for the idea Nigel, however, I still ran into another issue with the cursor variable substitution where it complained

call cursor_ROWPERROW(): Error Code: 1054. Unknown column 'zzzzz' in 'where clause'

So basically, it was not able to substitute the cursor value so I followed the same approach you mentioned and put quotes around for text, hence with select query to see what query it was trying to generate I came with the below SQL which solved my issue.

DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER $$
CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
    DECLARE cursor_COL VARCHAR(50);
    DECLARE cursor_TAB VARCHAR(50);
    DECLARE tab VARCHAR(100);
    DECLARE col1 VARCHAR(50);
    DECLARE col2 VARCHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE cursor_i CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='xxxxx' AND TABLE_NAME='yyyyy' AND COLUMN_NAME = 'zzzzz';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cursor_i;
    read_loop: LOOP
        FETCH cursor_i INTO cursor_COL, cursor_TAB;
        SET tab := CONCAT('PD', '_', cursor_TAB, '_' , cursor_COL);
        SET col1 := CONCAT(cursor_COL);
        SET col2 := CONCAT(cursor_COL, '_', 'value');
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @dyn_sql := CONCAT('CREATE TABLE ',tab,' AS SELECT Codevalue as ',col1,', valueLabel as ',col2,' from valuesets14 where varName = "',cursor_COL,'" and TableName = "',cursor_TAB,'";');
        PREPARE stmt_dyn_view FROM @dyn_sql;
        #SELECT @dyn_sql;
        EXECUTE stmt_dyn_view;
        DEALLOCATE PREPARE stmt_dyn_view;
    END LOOP;
    CLOSE cursor_i;
END;
$$

DELIMITER ;

Upvotes: 0

Nigel Ren
Nigel Ren

Reputation: 57141

The problem is your mixing up the various ways of referring to variables. Should omit the @ symbol when you've declared them...

read_loop: LOOP
    FETCH cursor_i INTO cursor_COL, cursor_TAB;
    SET tab := CONCAT('PD', '_', cursor_TAB, '_' , cursor_COL);
    SET col1 := CONCAT(cursor_COL);
    SET col2 := CONCAT(cursor_COL, '_', 'value');
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET @dyn_sql := CONCAT('CREATE TABLE ',tab,' AS SELECT Codevalue as ',col1,', valueLabel as ',col2,' from valuesets14 where varName = cursor_COL and TableName = cursor_TAB;');
    PREPARE stmt_dyn_view FROM @dyn_sql;
    EXECUTE stmt_dyn_view;
    DEALLOCATE PREPARE stmt_dyn_view;
END LOOP;

Upvotes: 1

Related Questions