Page 1 of 1

I need to select from a dynamic table name

Posted: Mon Apr 20, 2009 5:43 pm
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?

Re: I need to select from a dynamic table name

Posted: Mon Apr 20, 2009 6:10 pm
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;

Re: I need to select from a dynamic table name

Posted: Mon Apr 20, 2009 6:15 pm
by Benjamin
Pretty cool, I had a feeling it wasn't even possible.