MySQL Stored Procedures : Use a variable as the database name in a cursor declaration

[Vijay Jadhav]

I need to use a variable to indicate what database to query in the declaration of a cursor. Here is a short snippet of the code :

CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)

cdr_records:BEGIN

DECLARE cdr_record_cursor CURSOR FOR

SELECT cdrs_id, called, calling FROM dbName.cdrs WHERE lrn_checked = 'N';

# Setup logging
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
#call log_debug('Got exception in update_cdrs_lnp_data');
SET returnCode = -1;
END;


As you can see, I'm TRYING to use the variable dbName to indicate in which database the query should occur within. However, MySQL will NOT allow that. I also tried things such as :



CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)

cdr_records:BEGIN

DECLARE cdr_record_cursor CURSOR FOR

SET @query = CONCAT("SELECT cdrs_id, called, calling FROM " ,dbName, ".cdrs WHERE lrn_checked = 'N' ");
PREPARE STMT FROM @query;
EXECUTE STMT;

# Setup logging
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
#call log_debug('Got exception in update_cdrs_lnp_data');
SET returnCode = -1;
END;


This also dosen't work.

Can anyone think of a way to use the same stored procedure in multiple databases by passing in the name of the db that should be affected?