declare @filerq varchar(10)
declare @filename1 varchar(15)
declare @filename2 varchar(15)
declare @filename3 varchar(15)
set @filerq=left(convert(varchar,getdate(),120),4)+substring(convert(varchar,getdate(),120),6,2)
set @filename1='erp.mz_sf'+@filerq
set @filename2='erp.mz_fy'+@filerq
set @filename3='erp.mz_bh'+@filerq
select * from @filename1
select * from @filename2
select * from @filename3出现的错误
服务器: 消息 137,级别 15,状态 2,行 10
必须声明变量 '@filename1'。
服务器: 消息 137,级别 15,状态 1,行 11
必须声明变量 '@filename2'。
服务器: 消息 137,级别 15,状态 1,行 11
必须声明变量 '@filename3'。该如何处理?
declare @filename1 varchar(15)
declare @filename2 varchar(15)
declare @filename3 varchar(15)
set @filerq=left(convert(varchar,getdate(),120),4)+substring(convert(varchar,getdate(),120),6,2)
set @filename1='erp.mz_sf'+@filerq
set @filename2='erp.mz_fy'+@filerq
set @filename3='erp.mz_bh'+@filerq
select * from @filename1
select * from @filename2
select * from @filename3出现的错误
服务器: 消息 137,级别 15,状态 2,行 10
必须声明变量 '@filename1'。
服务器: 消息 137,级别 15,状态 1,行 11
必须声明变量 '@filename2'。
服务器: 消息 137,级别 15,状态 1,行 11
必须声明变量 '@filename3'。该如何处理?
declare @filename1 varchar(15)
declare @filename2 varchar(15)
declare @filename3 varchar(15)
set @filerq=left(convert(varchar,getdate(),120),4)+substring(convert(varchar,getdate(),120),6,2)
set @filename1='erp.mz_sf'+@filerq
set @filename2='erp.mz_fy'+@filerq
set @filename3='erp.mz_bh'+@filerq
exec('select * from '+@filename1)
exec('select * from '+@filename2)
exec('select * from '+@filename3)
declare @filename1 varchar(15)
declare @filename2 varchar(15)
declare @filename3 varchar(15)
set @filerq=left(convert(varchar,getdate(),120),4)+substring(convert(varchar,getdate(),120),6,2)
set @filename1='erp.mz_sf'+@filerq
set @filename2='erp.mz_fy'+@filerq
set @filename3='erp.mz_bh'+@filerqSelect KSBM,KS=Min(KS),
LJFY = Sum(FY),
RC = Count(Distinct LSH),
CZTS=Count(Distinct RQ),
PJRZRC=Count(Distinct LSH)*1.0 / Count(Distinct RQ),
RJFY = Sum(FY) /Count(Distinct LSH) into bb
from
(
SELECT RQ=SF.SFRQ,
ZD=BH.ZD,
BZ=ISNULL(ZDJB.MC,'不确定'),
KSBM=BH.KSBM,
KS=ISNULL(ZDKS.MC,'不确定'),
YSBM=SF.YS,
YS=ISNULL(ZDRY.MC ,'不确定'),
FB=ISNULL(ZDFB.MC,'不确定'),
LSH=BH.LSH,
FY=FY.FY
FROM @filename3 BH
JOIN @filename1 SF
ON BH.LSH=SF.LSH
JOIN @filename2 FY
ON SF.SFID=FY.SFID AND FY.HSFF=4 AND FYLB=1
LEFT JOIN HIS.ZD_KS ZDKS ON BH.KSBM=ZDKS.BM
LEFT JOIN HIS.ZD_RY ZDRY ON ZDRY.BM=SF.YS
JOIN HIS.ZD_RYFB ZDFB ON ZDFB.BM=BH.FB
LEFT JOIN HIS.ZD_JB ZDJB ON ZDJB.BM=BH.ZD
WHERE SF.STATE & 0x85=0x04
AND SF.SFRQ>=datediff(dd,'18991230',getdate())-5 AND SF.SFRQ<=datediff(dd,'18991230',getdate())-5 and BH.FB between 1 and 156
) t Group by KSBM exec master.dbo.xp_cmdshell 'BCP MZSF.dbo.bb out c:\a.txt /T /c /t,'drop table bb
declare @filename1 varchar(15)
declare @filename2 varchar(15)
declare @filename3 varchar(15)
set @filerq=left(convert(varchar,getdate(),120),4)+substring(convert(varchar,getdate(),120),6,2)
set @filename1='erp.mz_sf'+@filerq
set @filename2='erp.mz_fy'+@filerq
set @filename3='erp.mz_bh'+@filerq
select @sql='
Select KSBM,KS=Min(KS),
LJFY = Sum(FY),
RC = Count(Distinct LSH),
CZTS=Count(Distinct RQ),
PJRZRC=Count(Distinct LSH)*1.0 / Count(Distinct RQ),
RJFY = Sum(FY) /Count(Distinct LSH) into bb
from
(
SELECT RQ=SF.SFRQ,
ZD=BH.ZD,
BZ=ISNULL(ZDJB.MC ,''不确定''),
KSBM=BH.KSBM,
KS=ISNULL(ZDKS.MC ,''不确定''),
YSBM=SF.YS,
YS=ISNULL(ZDRY.MC ,''不确定''),
FB=ISNULL(ZDFB.MC ,''不确定''),
LSH=BH.LSH,
FY=FY.FY
FROM '+@filename3+' BH
JOIN '+@filename1+' SF
ON BH.LSH=SF.LSH
JOIN '+@filename2+' FY
ON SF.SFID=FY.SFID AND FY.HSFF=4 AND FYLB=1
LEFT JOIN HIS.ZD_KS ZDKS ON BH.KSBM=ZDKS.BM
LEFT JOIN HIS.ZD_RY ZDRY ON ZDRY.BM=SF.YS
JOIN HIS.ZD_RYFB ZDFB ON ZDFB.BM=BH.FB
LEFT JOIN HIS.ZD_JB ZDJB ON ZDJB.BM=BH.ZD
WHERE SF.STATE & 0x85=0x04
AND SF.SFRQ>=datediff(dd,''18991230'',getdate())-5 AND SF.SFRQ<=datediff(dd,''18991230'',getdate())-5 and BH.FB between 1 and 156
) t Group by KSBM'exec(@sql)exec master.dbo.xp_cmdshell 'BCP MZSF.dbo.bb out c:\a.txt /T /c /t,'drop table bb