先把执行的语句转换为一句sql实现查询功能。---其中执行的语句,其中uf_sumbill(是建的一个表值函数) exec sp_executesql N'select * from uf_sumbill(@P1,@P2)',N'@P1 datetime,@P2 datetime','2000-03-01 00:00:00:000' uf_sumbill里面的语句---------select isnull(sum(b.pjmoney),0) from v_mlinout as b where (a.deptid=b.depotid)
and(b.CheckDate > @endDate )and b.depotid=86 其中:v_mlinout 为视图,语句为:SELECT a.ID, a.InvoiceCode, a.MLCode, a.Name AS MLName, a.PYCode, a.WBCode, a.UnitID, a.PriceRate, a.PDCode, a.PVCode, a.Spec, a.JobNumber,
a.effdate, a.ClassID, a.PjPrice, a.LjPrice, a.Amount, a.PjMoney, a.LjMoney, b.FromDeptID, b.FromDeptName, b.Typer, b.Tag, b.DepotID, b.iomcode,
b.CreateDate, b.CreaterID, b.Creater, b.Checker, b.CheckerID, b.CheckDate FROM dbo.MEMMLInOutDetail AS a INNER JOIN
dbo.MEMMLInOutMaster AS b ON a.IOMID = b.ID
and(b.CheckDate > @endDate )and b.depotid=86 其中:v_mlinout 为视图,语句为:SELECT a.ID, a.InvoiceCode, a.MLCode, a.Name AS MLName, a.PYCode, a.WBCode, a.UnitID, a.PriceRate, a.PDCode, a.PVCode, a.Spec, a.JobNumber,
a.effdate, a.ClassID, a.PjPrice, a.LjPrice, a.Amount, a.PjMoney, a.LjMoney, b.FromDeptID, b.FromDeptName, b.Typer, b.Tag, b.DepotID, b.iomcode,
b.CreateDate, b.CreaterID, b.Creater, b.Checker, b.CheckerID, b.CheckDate FROM dbo.MEMMLInOutDetail AS a INNER JOIN
dbo.MEMMLInOutMaster AS b ON a.IOMID = b.ID
and(b.CheckDate > @endDate )and b.depotid=86
楼主是不是没有给全,那个 a.deptid 是怎么来的!
RETURNS @test TABLE
( deptid int default 0,
deptname varchar(40),
deptcode varchar(20),
库房 int default 0,
原存 money default 0,
入库 money default 0,
出库 money default 0,
结存 money default 0,
备注 Varchar(50)
)
as
begin
insert into @test(deptcode,deptid,deptname,库房)
select distinct deptcode,deptid,DeptName,(设备库房 + 配件库房 + 材料库房) from dbo.uf_dept_mem()
where 设备库房=1 or 配件库房=1 or 材料库房=1
order by deptcode
update a
set a.结存=(select isnull(sum(b.pjmoney),0) from memmldepot b where b.depotid=a.deptid)
from @test as a update a
set a.原存 =(select isnull(sum(b.pjmoney),0) from v_mlinout as b where (a.deptid=b.depotid)
and(b.CheckDate > @endDate )and b.depotid in (select deptid from @test))
from @test as a update a
set a.入库 =(select isNull(sum(b.pjmoney),0) from v_mlinout as b where a.deptid=b.depotid
and b.tag='入' and (b.CheckDate between @stDate and @endDate )and b.depotid in (select deptid from @test))
from @test as a
update a
set a.出库 =(select isnull(sum(b.pjmoney),0) from v_mlinout as b where a.deptid=b.depotid
and b.tag='出' and (b.CheckDate between @stDate and @endDate )and b.depotid in (select deptid from @test))
from @test as a
--update a
--set a.结存=a.结存 - a.原存
--from @test as a update a
set a.原存 =a.结存 - a.入库 + a.出库
from @test as a
update @test
set 库房 =1
where 库房>0 Returnend