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（）），11​​2）

BEGIN

SELECT *

来自OPENQUERY（['+ @LinkedServerName +']，'''+ @DynamicSQL +'''）

UNION ALL

SELECT *

FROM #temp

end

else IF（201404 + 99）<> CONVERT（nvarchar（6），dateadd（月，-1，GETDATE（）），11​​2）

开始



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