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