MySQL Stored Procedures : Use a variable as the database name in a cursor declaration
- by Justin
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;
Of course this doesn't work either as MySQL only allows a standard SQL statement in the cursor declaration.
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?
