Solved

Mysql Stored Procedure Dynamic Paramter For Cursor

Posted on 2009-07-14
1
1,785 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
1 Comment
 

Accepted Solution

by:
erenpasa earned 0 total points
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now