Solved

Mysql Stored Procedure Dynamic Paramter For Cursor

Posted on 2009-07-14
1
1,797 Views
Last Modified: 2013-12-13
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
Comment
Question by:erenpasa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
erenpasa earned 0 total points
ID: 24849999
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question