sql must declare scalar variable

See more:

SQL

I get the must declare scalar variable @FirstTableMonth error.

Here is my code:

alter PROCEDURE stp3_getDataFrom11Tables
(
@FirstTableMonth int,
@LinkedServerName sysname = 'Server1'
)
AS

DECLARE
@DynamicSQL nvarchar(max) = '',
@DynamicSQL2 nvarchar(max) = '',
@OpenQuerySql nvarchar(max),
@Table_Name sysname,
@Table_Name2 sysname,
@TableMonth int,
@TableMonth2 int ,
@CurrentMonth int = 0,
@NextYearMonth int = 1,
@part2 nvarchar(max) = ''


WHILE @CurrentMonth < 11
BEGIN

SELECT @TableMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN
@FirstTableMonth + @CurrentMonth
ELSE
@FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonth
END,
@NextYearMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN
@NextYearMonth
ELSE
@NextYearMonth + 1
END,
@Table_Name = 'xx'+CAST(@TableMonth as varchar)+'_T' ,

@TableMonth2 = @TableMonth+ 1,
@Table_Name2 = 'xx'+CAST(@TableMonth2 as varchar)+'_T' ,
--@TableMonth2 = CAST(@TableMonth2 as varchar),
--print @TableMonth2


@DynamicSQL = @DynamicSQL + 'SELECT *
FROM '+ @Table_Name + ' WHERE co_cde = 15
and cust_no in (123
) '+ CASE WHEN @CurrentMonth < 10 THEN ' UNION ALL ' ELSE '' END ,


@DynamicSQL2 = @DynamicSQL2 + 'SELECT *
FROM '+ @Table_Name2 + ' where
cust_no in (123
) ' + CASE WHEN @CurrentMonth < 10 THEN ' UNION ALL ' ELSE '' END ,


@part2 = @part2 + 'SELECT *
FROM OPENQUERY(Server1," SELECT *
FROM xx
where cust_no in (123
) "

)'



SET @CurrentMonth = @CurrentMonth + 1
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL BEGIN DROP TABLE #TEMP END
--last months snapshot
SELECT *,
cast(ODATE as datetime) as open_date
into #temp
FROM OPENQUERY(Server1,' SELECT *
cast(ODATE as datetime) as open_date
FROM xx WHERE cust_no in (123
) '
)
--if equal to month + 99 = previous month then union 11 tables with last months snapshot
--else
--union 11 tables with 12th table
SET @OpenQuerySql = 'IF (@FirstTableMonth+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)
BEGIN
SELECT *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )
UNION ALL
SELECT *
FROM #temp
end
else IF (201404+99) <> CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)
begin

SELECT *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )
UNION ALL
SELECT *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' )
end
'
END
EXEC sp_executesql @OpenQuerySql
GO