Sub select using table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Sub select using table name

Author

Topic

xzibited999
Starting Member

6 Posts

Posted - 2014-08-06 : 10:39:36

I have a table that has a list of tables within the database, trying to run a query that uses the value of those table names to run a sub select. The verbose version of the query is below...hoping someone can give me proper syntax for this.

Select t1.tableID, t1.tableName, (select max(recordID) from t1.tableName)
from listOfTables t1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-06 : 11:08:11

It looks like you would need "dynamic SQL". This is nothing more than using SQL to generate the SQL code you want to execute. There is no way to have a variable table name in a SQL statement. You want something along the lines of:

declare @dynSql varchar(max) = '';
select @dynSql = @dynSql + 'select ' + tableID + ', ' + tableName + ', (select max(recordID) from ' + tableName + ');' + char(13) + char(10)
from listOfTables;

print @dynSql  -- Debugging: Comment this line out if you are happy with the results
exec sp_executesql @dynSql;'

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources