Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1814
  • Last Modified:

Mysql Stored Procedure Dynamic Paramter For Cursor

Hi to all,
I want to pass a db name ChangeCreationDate function to alter all tables of db which passed via parameters.
But i take an error Unknown database 'dbName'.. I can't use database name dynamically. How can i do this?

Thanks
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `ChangeCreationDate`$$
 
CREATE  PROCEDURE `ChangeCreationDate`(dbName VARCHAR(50))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE tableName TINYTEXT;
  DECLARE cur1 CURSOR FOR SHOW TABLES FROM dbName; //i need at here db name passes via parameter.
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO tableName;
    SELECT tableName;
    IF NOT done THEN
		IF SUBSTRING(tableName,1,3)='fd_' THEN
			IF v5_versioning.ColumnIsExists(dbName,tableName,'creation_date') THEN
				SET @ssql=CONCAT('ALTER TABLE ',dbName,'.',tableName,' CHANGE creation_date created_date TIMESTAMP');
				SELECT @ssql;
				/*
				PREPARE stmt FROM @ssql;
				EXECUTE stmt;
				*/
			END IF;
		END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
END$$
 
DELIMITER ;

Open in new window

0
erenpasa
Asked:
erenpasa
1 Solution
 
erenpasaAuthor Commented:
I've change the line with
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=dbName;

it's okay.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now