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

Question

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?

Answer 1

No, you can't do that in cursors. Maybe just prepared statements may do the job? :

delimiter ;;

create procedure test(in dbName varchar(40))
begin

set @query := CONCAT("SELECT * FROM " , dbName, ".db;");

PREPARE s from @query;

EXECUTE s;
DEALLOCATE PREPARE s;

end;;

delimiter ;

call test("mysql");

Answer 2

The answer to this is that it cannot be done. You cannot use variables in the cursor declaration. I appreciate noonex's response. However, his solution does not allow me to walk through the results. It simply executes the query.

Answer 3

Try to create (temporary) table using prepared statement in a different procedure.

... SET @query = CONCAT("CREATE TABLE temp_table AS SELECT cdrs_id, called, calling FROM " ,dbName, ".cdrs WHERE lrn_checked = 'N' "); ...

And then select data from that table in your 'test' procedure.