MySQL Forums
Forum List  »  Stored Procedures

MySQL Stored Procedures : Use a variable as the database name in a cursor declaration
Posted by: Vijay Jadhav
Date: January 13, 2010 06:22AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Stored Procedures : Use a variable as the database name in a cursor declaration
4722
January 13, 2010 06:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.