MySQL set database name dynamically and select db.schematable

Question

I am creating a stored procedure, which takes a number of input parameters. I would like to be able to select which database to use, whereafter depending on the conditions as shown below, I would like to use the correct where conditions.

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.

I'm using Navicat and have defined my parameters in the IDE, my input params are :

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

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?

Tom · Accepted Answer · 2015-05-08 14:04:56Z

1

You can do this with a prepared statement.

A very basic prepared statement looks something like this:

SET @stat = CONCAT('SELECT * FROM ', @tab'); 

PREPARE stmt FROM @stat; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

EDIT: Full fledged example of the most basic statement, use if statements to set your variables for the 'where' and 'table' clauses and you should be fine!

EDIT2: Figured you didn't want to use the statements using incoming parameters, edited query.

EDIT3: Basically finished the stored procedure, you can call the procedure using CALL test('dbname.tablename') The parameter you provide here is the 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 THEN 
SET conditions = CONCAT("customername = '", icustomername, "'");

ELSEIF len(ipostcode) > 0 THEN 
SET conditions = CONCAT("postcode = '", ipostcode, "'");

ELSE SET conditions = CONCAT("accountnumber = '", iaccountnumber , "'");
END IF;

    SET @stat = CONCAT('SELECT ', cols, ' FROM ', tbl, ' WHERE ', conditions );
    PREPARE stmt from @stat;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

-------
CALL test('tablename');

edited May 8, 2015 at 14:04

answered May 8, 2015 at 11:12

Tom

3322 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Harry Over a year ago

So for my query will each select be a separate @stat?

Tom Over a year ago

From what I see, all your queries are basically the same. You can keep one @stat to execute and set the tablename and the where statement dynamically with your variables.

Harry Over a year ago

This statement will be wrapped within a BEGIN END?

Tom Over a year ago

I just whipped up the very basic statement which I think you can use very well, I'll edit it in my answer.

Harry Over a year ago

I see but how do I still set the db dynamically? I will have like 20 dbs? because the db name passed can be different each time.

|

Collectives™ on Stack Overflow

MySQL set database name dynamically and select db.schematable

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related