日期:2014-05-18 浏览次数:20420 次
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P') DROP PROC user_RecordInResult GO Create PROCEDURE user_RecordInResult @charWhere nvarchar(1000) AS declare @sql varchar(8000) Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int declare @sInvCode varchar(50),@eInvCode varchar(50) if object_id('tempdb..resultTemp') is not null drop table tempdb..resultTemp if LTRIM(RTRIM(@charwhere))<>'' begin SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10 SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10 IF @B1>10 SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1) IF @B2>10 SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2) end select cInvName,cInvStd,cComUnitName , case when mon=1 then sum(isum) else 0 end '1月出',case when mon=2 then sum(isum) else 0 end '2月出', case when mon=3 then sum(isum) else 0 end '3月出',case when mon=4 then sum(isum) else 0 end '4月出', case when mon=5 then sum(isum) else 0 end '5月出',case when mon=6 then sum(isum) else 0 end '6月出', case when mon=7 then sum(isum) else 0 end '7月出',case when mon=8 then sum(isum) else 0 end '8月出', case when mon=9 then sum(isum) else 0 end '9月出',case when mon=10 then sum(isum) else 0 end '10月出', case when mon=11 then sum(isum) else 0 end '11月出',case when mon=12 then sum(isum) else 0 end '12月出' into tempdb..resultTemp from(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon from rdrecord r join rdrecords s on r.id=s.id left join Inventory i on i.cInvcode=s.cInvCode left join computationUnit u on u.cComUnitCode=i.cComUnitCode where r.bRdFlag=0 and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0)) group by i.cInvName,i.cInvStd,u.cComUnitName,r.dDate ) a group by cInvName,cInvStd,cComUnitName,mon
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P') DROP PROC user_RecordInResult GO --判断该存储过程是否存在 Create PROCEDURE user_RecordInResult @charWhere nvarchar(1000) AS declare @sql varchar(8000) Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int declare @sInvCode varchar(50),@eInvCode varchar(50) if object_id('tempdb..resultTemp') is not null drop table tempdb..resultTemp if LTRIM(RTRIM(@charwhere))<>'' begin SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10 SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10 IF @B1>10 SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1) IF @B2>10 SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2) end --if else 传入参数的处理 select cInvName,cInvStd,cComUnitName , case when mon=1 then sum(isum) else 0 end '1月出',case when mon=2 then sum(isum) else 0 end '2月出', case when mon=3 then sum(isum) else 0 end '3月出',case when mon=4 then sum(isum) else 0 end '4月出', case when mon=5 then sum(isum) else 0 end '5月出',case when mon=6 then sum(isum) else 0 end '6月出', case when mon=7 then sum(isum) else 0 end '7月出',case when mon=8 then sum(isum) else 0 end '8月出', case when mon=9 then sum(isum) else 0 end '9月出',case when mon=10 then sum(isum) else 0 end '10月出', case when mon=11 then sum(isum) else 0 end '11月出',case when mon=12 then sum(isum) else 0 end '12月出' into tempdb..resultTemp from(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon from rdrecord r join rdrecords s on r.id=s.id left join Inventory i on i.cInvcode=s.cInvCode left join computationUnit u on u.cComUnitCode=i.cComUnitCode where r.bRdFlag=0 and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0))