本文介绍了sql必须声明标量变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我得到必须声明的标量变量@FirstTableMonth错误。 这是我的代码: 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那么 @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 GOI get the must declare scalar variable @FirstTableMonth error.Here is my code:alter PROCEDURE stp3_getDataFrom11Tables( @FirstTableMonth int, @LinkedServerName sysname = 'Server1')ASDECLARE @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 < 11BEGIN 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 + 1IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL BEGIN DROP TABLE #TEMP END--last months snapshotSELECT *,cast(ODATE as datetime) as open_date into #tempFROM 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 tableSET @OpenQuerySql = 'IF (@FirstTableMonth+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)BEGIN SELECT * FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )UNION ALLSELECT *FROM #tempendelse IF (201404+99) <> CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)beginSELECT * FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )UNION ALLSELECT * FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' )end'ENDEXEC sp_executesql @OpenQuerySqlGO推荐答案删除括号()在 stp3_getDataFrom11Tables 之后再试一次。Remove the brackets () after stp3_getDataFrom11Tables and try again.alter PROCEDURE stp3_getDataFrom11Tables@FirstTableMonth int=0,@LinkedServerName sysname = 'Server1'ASDECLARE@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 < 11BEGINSELECT @TableMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN@FirstTableMonth + @CurrentMonthELSE@FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonthEND,@NextYearMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN@NextYearMonthELSE@NextYearMonth + 1END,@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 = 15and cust_no in (123) '+ CASE WHEN @CurrentMonth < 10 THEN ' UNION ALL ' ELSE '' END ,@DynamicSQL2 = @DynamicSQL2 + 'SELECT *FROM '+ @Table_Name2 + ' wherecust_no in (123) ' + CASE WHEN @CurrentMonth < 10 THEN ' UNION ALL ' ELSE '' END ,@part2 = @part2 + 'SELECT *FROM OPENQUERY(Server1," SELECT *FROM xxwhere cust_no in (123) ")'SET @CurrentMonth = @CurrentMonth + 1IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL BEGIN DROP TABLE #TEMP END--last months snapshotSELECT *,cast(ODATE as datetime) as open_dateinto #tempFROM OPENQUERY(Server1,' SELECT *cast(ODATE as datetime) as open_dateFROM 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 tableSET @OpenQuerySql = 'IF (@FirstTableMonth+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)BEGINSELECT *FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )UNION ALLSELECT *FROM #tempendelse IF (201404+99) <> CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)beginSELECT *FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )UNION ALLSELECT *FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' )end'ENDEXEC sp_executesql @OpenQuerySqlGO 这篇关于sql必须声明标量变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
05-27 19:36
查看更多