SQL Server guy here but I'm trying to write a mySQL stored procedure which takes a temp table (from a sql dump) and merges it into an existing database.
The issue I'm having is that whilst the cursor does populate the first variable col_name it does not populate the other two - data_type and is_nullable.
I've separated out the core cursor part of the procedure to illustrate the issue.
According to the documentation and other posts I've found online, this should work.
Any thoughts from mySQL experts on what might be happening here?
DELIMITER $$;
CREATE PROCEDURE sp_Test()
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE col_name VARCHAR(64) DEFAULT "";
DECLARE data_type VARCHAR(64) DEFAULT "";
DECLARE is_nullable VARCHAR(3) DEFAULT "";
DECLARE column_cursor CURSOR FOR
SELECT `COLUMN_NAME`, `DATA_TYPE`, `IS_NULLABLE` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='country';
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
OPEN column_cursor;
build_query: LOOP
FETCH column_cursor INTO col_name, data_type, is_nullable;
IF finished = 1 THEN
LEAVE build_query;
END IF;
SELECT col_name, data_type, is_nullable;
END LOOP build_query;
CLOSE column_cursor;
END$$;
DELIMITER;