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
>
>
>
Attachment: [text/html]
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
