I need to select from a dynamic table name

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

I need to select from a dynamic table name

Post by Luke »

I am writing a stored procedure where I select a table name from a table. I am being forced to do this because the developer who wrote the app was a retard. Basically there is a table of table names I need to select from, giving myself a @table_name variable. Then I need to use that variable as the table name to select more data from. Something like this:

Code: Select all

DECLARE @table_name VARCHAR(50);
 
SELECT table_name
FROM my_tables
WHERE id = 4;
 
SELECT *
FROM @table_name
WHERE 1 = 1;
The problem is, when I do this, it assumes @table_name is a set of data. It expects @table_name to be a recordset. How do I make it use it as a string, so that it selects from the table name contained in @table_name?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: I need to select from a dynamic table name

Post by Luke »

This did the trick (although others should be aware of the possibility of SQL injection here)...

Code: Select all

DECLARE @table_name VARCHAR(50);
DECLARE @sql VARCHAR(150);
 
SELECT table_name
FROM my_tables
WHERE id = 4;
 
SELECT @sql = 'SELECT * FROM [' + @table_name + '] WHERE 1 = 1';
EXEC @sql;
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: I need to select from a dynamic table name

Post by Benjamin »

Pretty cool, I had a feeling it wasn't even possible.
Post Reply