vendredi 8 mai 2015

MySQL set database name dynamically and select db.schematable

I am creating a stored procedure, which takes a number of input parameters. Depending on the conditions as shown below, I would like to select the appropriate database to use.

I know you cannot use "USE" keyword in a stored proc and I have also tried to set @ds in the query but with not much success.

SQL

BEGIN
declare dbName varchar(255); --attempt at using db name passed in
select imemberdbname into dbName;
set @ds = concat(dbName,'.customers'); -- I have also tried this

if LENGTH(icustomername) > 0 THEN
    (Select customerid, customername, postcode, accountnumber from dbName.customers
        WHERE customername = icustomername);
ELSEIF len(ipostcode) > 0 THEN
    (Select customerid, customername, postcode, accountnumber from dbName.customers
        WHERE postcode = ipostcode);
ELSE 
    (Select customerid, customername, postcode, accountnumber from dbName.customers
        WHERE accountnumber = iaccountnumber);
end if;

END

I am unable to select the db.table in order to carry out the selects. Is there a way this is possible? or is there a better solution?

Aucun commentaire:

Enregistrer un commentaire