做了好久的一个通过条件查询进销存的存储过程

USE [DEV_WXT]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetPeriodInOutList]    Script Date: 08/19/2010 17:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[usp_GetPeriodInOutList]
(
    @strSiteCode nvarchar(200) ='',    
    @BeginDate nvarchar(100)='',
    @EndDate nvarchar(100)='',
    @Brand nvarchar(200)='',
    @MacNo nvarchar(200)=''
)

AS


declare @EndDate1 nvarchar(10)
select @EndDate1=convert(char(10),dateadd(day,1,@EndDate),121)
--print @EndDate1



---insert trx detail
select A.sitecode,a.trxno,a.closedate,b.fromsubinv as subinv,b.brand,b.itemno,
b.unitcost*b.applyqty as InAmount,b.applyqty as InQty,
convert(decimal(19,2),0) as OutAmount,0 as OutQty into #temp
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode=b.SiteCode  and a.TrxNo=B.TrxNo and a.SiteCode=@strSiteCode
and a.Status='AP' 
and b.FromSubinv='' and b.ToSubinv<>'' and (b.Brand=@Brand or @Brand='') 
and (b.ItemNo=@MacNo or @MacNo='')
and a.CloseDate<@EndDate1 AND b.ApplyQty>0

insert into #temp
select A.SiteCode,a.TrxNo,a.CloseDate,b.FromSubinv as subinv,b.Brand,b.ItemNo,
convert(decimal(19,2),0) as InAmount,0 as InQty,
b.UnitCost*(-b.ActualQty) as OutAmount,-b.ApplyQty as OutQty  
from dbo.SYS_TrxHeadHist a,dbo.SYS_TrxLineHist b
where a.SiteCode=b.SiteCode  and a.TrxNo=B.TrxNo and a.SiteCode=@strSiteCode
 AND a.Status='AP' 
and b.FromSubinv<>'' and b.ToSubinv='' and (b.Brand=@Brand or @Brand='')
and (b.ItemNo=@MacNo or @MacNo='')
and a.CloseDate<@EndDate1 AND b.ApplyQty<0






---得到期末数量，金额
select sitecode AS SITECODE,brand as Brand,itemno as MACNO,
convert(nvarchar(10),'') as Type,convert(nvarchar(100),'') as MACNAME,convert(nvarchar(4000),'') as Model,
0 as BeginQty,convert(decimal(19,2),0) as BeginAmount,
0 as PeriodInQty,convert(decimal(19,2),0) as PeriodInAmount,
0 as PeriodOutQty,convert(decimal(19,2),0) as PeriodOutAmount,
sum(InQty)-sum(OutQty) as EndQty,sum(InAmount)-sum(OutAmount) as EndAmount 
into #temp_r from #temp group by sitecode,brand,itemno


--更新期初数量，金额
select brand,itemno,
sum(InQty)-sum(OutQty) as BeginQty,sum(InAmount)-sum(OutAmount) as BeginAmount 
into #temp1 from #temp where closedate<@BeginDate group by brand,itemno

update #temp_r set BeginQty=b.BeginQty,BeginAmount=b.BeginAmount from #temp_r a,#temp1 b
where a.Brand=b.brand and a.MACNO=b.itemno

--更新本期入库数量，金额
select brand,itemno,
sum(InQty) as InQty,sum(InAmount) as InAmount 
into #temp2 from #temp where closedate>=@BeginDate group by brand,itemno

update #temp_r set PeriodInQty=b.InQty,PeriodInAmount=b.InAmount from #temp_r a,#temp2 b
where a.Brand=b.brand and a.MACNO=b.itemno


--更新本期出库数量，金额
select brand,itemno,
sum(OutQty) as OutQty,sum(OutAmount) as OutAmount 
into #temp3 from #temp where closedate>=@BeginDate group by brand,itemno

update #temp_r set PeriodOutQty=b.OutQty,PeriodOutAmount=b.OutAmount from #temp_r a,#temp3 b
where a.Brand=b.brand and a.MACNO=b.itemno

--更新配件类型，料品描述, 型号
update #temp_r set TYPE=b.TYPE,MACNAME=b.ItemDesc,MODEL=dbo.GetModelList(b.sitecode,b.BRAND,b.ItemNo)
 from #temp_r a,SYS_SiteItem b
WHERE a.BRAND=b.BRAND and a.macno=b.ItemNo

SELECT * FROM #temp_r order by BRAND,MACNO

drop table #temp_r,#temp,#temp1,#temp2,#temp3