CODEWITHSUNDEEP
sql-servervariableswhile-loopfetch
Fonsi
Fonsi

Reputation: 59

Pass Variable in SELECT FROM Loop

I'm looking to select from multiple tables (MainTbl) but it will be based on the result set (StateTbl) of which tables would be pulled.

MainTables dbo.TABLE_MO, dbo.TABLE_CA, dbo.TABLE_AL, dbo.TABLE_MI

Only looking to pull based on resultset StateTbl MO, CA, WA

Declare @Loop_Count int = 0
DECLARE @State varchar(2)
DECLARE @SQL varchar(max)

DECLARE db_cursor CURSOR FOR SELECT State FROM StateTbl

OPEN db_cursor

FETCH db_cursor INTO @State

WHILE (@@FETCH_STATUS = 0) 
BEGIN 


SET @SQL = 
'
    dbo.TABLE_'+ @State +'
'
EXEC(@SQL)

SET @Loop_Count = @Loop_Count + 1
FETCH db_cursor INTO @SQL

END
CLOSE  db_cursor
DEALLOCATE db_cursor

Upvotes: 0

Views: 731

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Instead of a loop you can leverage dynamic and the StateTbl to build your dynamic sql. Something like this.

declare @SQL nvarchar(max) = ''

select 'select * from TABLE_' + [State] + ' UNION ALL '
from StateTbl

select @SQL = left(@SQL, len(@SQL) - 10)

select @SQL
--uncomment the line below when you satisfied the dynamic sql is written the way you want it.
--exec sp_executesql @SQL

Upvotes: 1

Related Questions