Pass Variable in SELECT FROM Loop

Question

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

How is EXEC(@SQL) supposed to select from a table? @SQL will only contain the name of a table. Shouldn't you be doing SET @SQL = 'SELECT * FROM dbo.TABLE_'+ @State +' '? — TT.
– TT., Commented Sep 2, 2017 at 14:14
Possible Duplicate: stackoverflow.com/questions/20054854/… — Jay Wheeler
– Jay Wheeler, Commented Sep 2, 2017 at 15:27

Sean Lange · Accepted Answer · 2017-09-02 20:33:23Z

1

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

answered Sep 2, 2017 at 20:33

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Fonsi Over a year ago

I'm getting error Msg 537, Level 16, State 5, Line 6 Invalid length parameter passed to the LEFT or SUBSTRING function.

Sean Lange Over a year ago

Does the query return any rows? I wrote that super fast and didn't really test it. It would definitely return that error if there are no rows returned.

Fonsi Over a year ago

It returned rows. For now I've tunred it into a store procedure and doing an insert after an execution. Anyway to turn the last variable 'AAB' from hard code and use a select list. It would still need to run record by record. INSERT #RateMeasComp EXEC spMeasRateFlo '2','AAB'

Sean Lange Over a year ago

No offense but if you are stuck doing insert RBAR (row by agonizing row) you are doing something wrong. Inserts should be set based. Maybe you don't need that procedure for this.

Fonsi Over a year ago

Thanks Sean. Is there another better way to do this through SQL ? My only other option with this is the SSIS route with a foreach loop but was trying to just handle it in SQL, hence why I started with the loop route.

|

Collectives™ on Stack Overflow

Pass Variable in SELECT FROM Loop

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related