Dynamic SQL with varchar in replace of table variable

Question

I'm pretty new to Dynamic SQL, and SQL Server in general, but I would like to iterate over a table list within information_schema.tables. Each iteration has a varchar variable listing the Month-Year to be searched within the table name (where table_name like concat('USA_RETHHDs_%', @Month)).

Each table with the matched date would then be placed into a varchar variable. The problem is, I want to obtain the table variable from the information_schema.tables list instead, but am unsure of how this can be done. As a result, I'm trying to go the dynamic route, embedding the varchar variable into a dynamic SQL query. However, it's expecting a table variable rather than a varchar.

declare @Month varchar(6)
declare @Table varchar(32)
declare @sql nvarchar(max)
declare @MonthCount int

while @MonthCount > 0
    begin
        select @Month = DateSuffix from @DateTable where Row = @MonthCount
        select @Table = table_name from information_schema.tables where table_name like concat('USA_RETHHDs_%', @Month)
        select @Table = concat('dbo.', @Table)
        set @sql = 'select * from @Table'
        execute SP_EXECUTESQL @sql  

        select @MonthCount = @MonthCount - 1
    end

JamieD77 · Accepted Answer · 2015-07-06 21:34:02Z

2

Try

set @sql = 'select * from ' + @Table
execute SP_EXECUTESQL @sql

I should also mention that you can include the parameter in your sp_executesql call

DECLARE @Month VARCHAR(6)
DECLARE @Table VARCHAR(32)
DECLARE @sql NVARCHAR(MAX)
DECLARE @MonthCount INT
DECLARE @ParmDefinition NVARCHAR(500);


WHILE @MonthCount > 0 
    BEGIN
        SELECT  @Month = DateSuffix
        FROM    @DateTable
        WHERE   Row = @MonthCount

        SELECT  @Table = table_name
        FROM    information_schema.tables
        WHERE   table_name LIKE CONCAT('USA_RETHHDs_%',@Month)

        SELECT  @Table = CONCAT('dbo.',@Table)

        SET     @sql = N'select * from @param '
        SET     @ParmDefinition = N'@param varchar(500)'

        EXECUTE SP_EXECUTESQL @sql, @ParmDefinition, @param = @Table

        SELECT  @MonthCount = @MonthCount - 1
    END

edited Jul 6, 2015 at 21:34

answered Jul 6, 2015 at 21:25

JamieD77

14k1 gold badge19 silver badges27 bronze badges

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

1 Comment

baobobs Over a year ago

Excellent! So much easier than I thought. Thanks Jamie!

Collectives™ on Stack Overflow

Dynamic SQL with varchar in replace of table variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related