List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 12 2006 6:53pm
Subject:Re: Passing db names to a stored proc in MySQL 5
View as plain text  
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
Thread
Passing db names to a stored proc in MySQL 5Rory McKinley9 Feb
  • Re: Passing db names to a stored proc in MySQL 5Martijn Tonies9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Peter Brawley12 Feb
  • Re: Passing db names to a stored proc in MySQL 5SGreen9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Martijn Tonies9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Rory McKinley9 Feb
      • Re: Passing db names to a stored proc in MySQL 5SGreen9 Feb
        • Re: Passing db names to a stored proc in MySQL 5Rory McKinley10 Feb
        • Re: [SOLVED]Passing db names to a stored proc in MySQL 5Rory McKinley10 Feb
      • Re: Passing db names to a stored proc in MySQL 5Peter Brawley11 Feb