MySQL set database name dynamically and select db.schematable -

i creating stored procedure, takes number of input parameters. able select database use, whereafter depending on conditions shown below, use correct conditions.

i know cannot use "use" keyword in stored proc , have tried set @ds in query not success.

i'm using navicat , have defined parameters in ide, input params :

• icustomername varchar(255)
• ipostcode varchar(255)
• iaccountnumber varchar(255)
• imemberdbname varchar(255)

sql

begin declare dbname varchar(255); --attempt @ using db name passed in select imemberdbname dbname; set @ds = concat(dbname,'.customers'); -- have tried  if length(icustomername) > 0     (select customerid, customername, postcode, accountnumber dbname.customers         customername = icustomername); elseif len(ipostcode) > 0     (select customerid, customername, postcode, accountnumber dbname.customers         postcode = ipostcode); else      (select customerid, customername, postcode, accountnumber dbname.customers         accountnumber = iaccountnumber); end if;  end 

i unable select db.table in order carry out selects. there way possible? or there better solution?

you can prepared statement.

a basic prepared statement looks this:

set @stat = concat('select * ', @tab');   prepare stmt @stat;  execute stmt;  deallocate prepare stmt;  

edit: full fledged example of basic statement, use if statements set variables 'where' , 'table' clauses , should fine!

edit2: figured didn't want use statements using incoming parameters, edited query.

edit3: finished stored procedure, can call procedure using call test('dbname.tablename') parameter provide here tablename / dbname.tablename.

create definer=`user`@`%` procedure `test`(in tbl varchar(255)) begin declare cols varchar(255); declare conditions varchar(255);  set cols = 'customerid, customername, postcode, accountnumber';   if length(icustomername) > 0  set conditions = concat("customername = '", icustomername, "'");  elseif len(ipostcode) > 0  set conditions = concat("postcode = '", ipostcode, "'");  else set conditions = concat("accountnumber = '", iaccountnumber , "'"); end if;      set @stat = concat('select ', cols, ' ', tbl, ' ', conditions );     prepare stmt @stat;     execute stmt;     deallocate prepare stmt; end  ------- call test('tablename');