Re: Passing db names to a stored proc in MySQL 5

[Peter Brawley]

Rory, Martin >>CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) >>BEGIN >>SELECT * FROM test_db_name.test_table; >>END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? >No. Yes: DELIMITER | CREATE PROCEDURE testStoredProc( IN test_db_name CHAR(64) ) BEGIN   SET @sql = CONCAT( "SELECT * FROM ", test_db_name, ".test_table" );   PREPARE stmt FROM @sql;   EXECUTE stmt;   DEALLOCATE PREPARE stmt; END; | DELIMITER ; PB ----- Martijn Tonies wrote: I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? No. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]