MySQL Variable Database Name Syntax Error

Question

I'm trying to set a database name for a MySQL query. I've been able to get my first query to work, but the second I'm struggling to figure out where my syntax error is. I'm guessing it has something to do with the variable, but I'm stuck.

SET @db = CONCAT('SELECT client_databases.database_name
FROM client_databases
JOIN jobs ON jobs.organisation_id = client_databases.organisation_id
WHERE jobs.transaction_reference="K01-REC0000001"');

SET @q = CONCAT('SELECT receipts_lines.id, product_code, product_name, receipts_lines.is_putaway
    FROM ', @db ,'.receipts
    JOIN ', @db ,'.receipts_lines ON receipts_lines.receipt_id = receipts.id
    WHERE reference_number="K01-REC0000001"');

PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

What's the database name? You are using a whole query as the value of @db. — The Impaler
– The Impaler, Commented Jan 12, 2020 at 15:11

nbk · Accepted Answer · 2020-01-12 15:23:57Z

1

You should do something like this. Where you select into @db the databasename and use this to build your stmt

SELECT client_databases.database_name INTO @db
FROM client_databases
JOIN jobs ON jobs.organisation_id = client_databases.organisation_id
WHERE jobs.transaction_reference="K01-REC0000001";

SET @q = CONCAT('SELECT receipts_lines.id, product_code, product_name, receipts_lines.is_putaway
    FROM ', @db ,'.receipts
    JOIN ', @db ,'.receipts_lines ON receipts_lines.receipt_id = receipts.id
    WHERE reference_number="K01-REC0000001"');

PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Jan 12, 2020 at 15:23

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

MySQL Variable Database Name Syntax Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related