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
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