sql必须声明标量变量 [英] sql must declare scalar variable
问题描述
我得到必须声明的标量变量@FirstTableMonth错误。
这是我的代码:
alter PROCEDURE stp3_getDataFrom11Tables
(
@FirstTableMonth int,
@ LinkedServerName sysname ='Server1'
)< br $>
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那么
@FirstTableMonth + @CurrentMonth
ELSE
@FirstTableMonth + 100 - (@ FirstTableMonth%100)+ @NextYearMonth
END,
@NextYearMonth = CASE WHEN(@FirstTableMonth + @CurrentMonth)%100< 13那么
@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
和cust_no in(123
)'+ CASE WHEN @CurrentMonth< 10那么'UNION ALL'ELSE''结束,
@ DynamicSQL2 = @ DynamicSQL2 +'SELECT *
FROM'+ @ Table_Name2 +'其中
cust_no in(123
)'+ CASE WHEN @CurrentMonth< 10那么'UNION ALL'ELSE''结束,
@ 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
)'
)
- 如果等于月+ 99 =上个月然后工会11桌与上个月快照
--else
- 联盟11桌与第12桌
SET @OpenQuerySql ='IF(@ FirstTableMonth + 99)= CONVERT(nvarchar(6),dateadd(month,-1,GETDATE()),112)
BEGIN
SELECT *
来自OPENQUERY(['+ @LinkedServerName +'],'''+ @DynamicSQL +''')
UNION ALL
SELECT *
FROM #temp
end
else IF(201404 + 99)<> CONVERT(nvarchar(6),dateadd(月,-1,GETDATE()),112)
开始
SELECT *
来自OPENQUERY(['+ @LinkedServerName +'],'''+ @DynamicSQL +''')
UNION ALL
SELECT *
来自OPENQUERY(['+ @LinkedServerName +'],'''+ @ DynamicSQL2 +''')
结束
'
结束
EXEC sp_executesql @OpenQuerySql
GO
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
推荐答案
删除括号()在 stp3_getDataFrom11Tables
之后再试一次。
Remove the brackets () after stp3_getDataFrom11Tables
and try again.
alter PROCEDURE stp3_getDataFrom11Tables
@FirstTableMonth int=0,
@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
这篇关于sql必须声明标量变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!