CODEWITHSUNDEEP
mysql
helion3
helion3

Reputation: 37331

mysql user-defined variable in query

I'm trying to use a user-defined variable to stand-in for a table name so that I can just use mysql and not require any other languages.

This example is way simplistic - the real script uses the table name a LOT and it'd be easier for end-users to be able to just change the variable rather than search/replace.

SET @tablename = 'mytable';
SELECT * FROM @tablename;

This won't work, but shows the end result I need.

Upvotes: 3

Views: 5085

Answers (1)

RandomSeed
RandomSeed

Reputation: 29749

Use prepared statements:

SET @tablename = 'mytable';

SET @query = CONCAT('SELECT * FROM ', @mytable);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

If your actual query is far more complicated, you could wrap the building of the query in some stored function:

DELIMITER //
CREATE FUNCTION GetQuery(tableName VARCHAR(50)) RETURNS VARCHAR(100)
BEGIN
    DECLARE finalQuery VARCHAR(100);
    SET finalQuery = CONCAT('SELECT * FROM ', tableName);
    -- do something fancier with finalQuery here
    RETURN finalQuery;
END //
DELIMITER ;

SET @query = GetQuery('mytable');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Even better: do everything in a stored procedure:

DELIMITER //
CREATE PROCEDURE QueryTable(tableName VARCHAR(50))
BEGIN
    SET @finalQuery = CONCAT('SELECT * FROM ', tableName);
    PREPARE stmt FROM @finalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @finalQuery = NULL;
END //
DELIMITER ;

CALL QueryTable('mytable');

Upvotes: 5

Related Questions