CODEWITHSUNDEEP
mysqlcursor
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

MySQL: Cursor for select from temp table

I need to select column from temp table in stored procedure for cursor.

Here is the following script of my attempt:

Attempt:

DELIMITER $$
CREATE PROCEDURE `sptest2`(IN nm VARCHAR(50),IN sub VARCHAR(50))
BEGIN

DECLARE dp char(50);
DECLARE colmn_list varchar(100);


SET @q = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tem AS 
(SELECT DISTINCT ', nm ,' AS Col FROM table1)');

PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DECLARE cursor_n CURSOR for select col from tem;

Note: This gives me error. Because we can't declare cursor after some operations. We need to declare it after the BEGIN.

So what is the solution for this?

Upvotes: 1

Views: 3311

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

All declarations must be placed at begin of procedure:

DELIMITER $$
CREATE PROCEDURE `sptest2`(IN nm VARCHAR(50),IN sub VARCHAR(50))
BEGIN

DECLARE dp char(50);
DECLARE colmn_list varchar(100);
DECLARE cursor_n CURSOR for select col from tem;


SET @q = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tem AS 
(SELECT DISTINCT ', nm ,' AS Col FROM table1)');

PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But depending on your requirements, is a better solution to use subqueries.

May be is a good idea drop table before try to create it because it function will be called more than one time.

DROP TEMPORARY TABLE IF EXISTS tem;

Upvotes: 3

Related Questions