存儲過程傳回表

alter procedure proc_xsddgz_lj

as

--聲明變量

--銷售訂單

begin

declare @fbillno nvarchar(255)

declare @ddfentryid int

declare @fhdfentryid int

create table #t(ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int)

create table #tt(ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int)

create table #ttt(ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int)

create table #tttt(ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int)

declare @t table(ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int)

--declare @proc_xsddgz_lj table(

--ddid int,fhdid int ,ckdid int,zgysdid int,cwysdid int

--)

set @fbillno = 'ra181274390'

insert into #t(ddid)select soe.fentryid

from t_sal_order so

inner join dbo.t_sal_orderentry soe on so.fid = soe.fid

--where so.fbillno = @fbillno

--select *from #t

--drop table #t

--drop table #tt

--發貨單

insert into #tt(ddid,fhdid)

select t.ddid,fhd.fentryid from #t t left join

(

select sde.fentryid,sdel.fsid from dbo.t_sal_deliverynoticeentry sde

inner join t_sal_deliverynoticeentry_lk sdel on sde.fentryid = sdel.fentryid

inner join t_sal_deliverynotice sd on sd.fid = sde.fid

where sd.fdocumentstatus='c' )fhd on t.ddid = fhd.fsid

truncate table #t

insert into #t select *from #tt

truncate table #tt

--出庫單

insert into #tt (ddid,fhdid,ckdid)

select t.ddid,t.fhdid,ck.fentryid from #t t left join (

select soe.fentryid,soel.fsid,soe.factqty from dbo.t_sal_outstockentry soe

inner join dbo.t_sal_outstockentry_lk soel on soe.fentryid = soel.fentryid

inner join t_sal_outstock so on so.fid = soe.fid

where so.fdocumentstatus = 'c') ck on ck.fsid =t.fhdid

--暫估應收

insert into #ttt (ddid,fhdid,ckdid,zgysdid)

select t.ddid,t.fhdid,t.ckdid ,zgys.fentryid from #t t left join (

select ree.fentryid,relk.fsid,ree.fpriceqty from dbo.t_ar_receivable re

inner join dbo.t_ar_receivableentry ree on re.fid = ree.fid

inner join dbo.t_ar_receivableentry_lk relk on ree.fentryid = relk.fentryid

where re.fsetaccounttype=2 and re.fdocumentstatus = 'c' and ree.fsourcetype='sal_outstock')

zgys on zgys.fsid = t.ckdid

insert into #t select *from #ttt

truncate table #ttt

--财務應收

insert into #tttt (ddid,fhdid,ckdid,zgysdid,cwysdid)

select t.ddid,t.fhdid,t.ckdid ,t.zgysdid,cwys.fentryid from #t t left join (

select ree.fentryid,relk.fsid,ree.fpriceqty,re.fsetaccounttype from dbo.t_ar_receivable re

where re.fsetaccounttype=3 and re.fdocumentstatus = 'c')

cwys on cwys.fsid = t.zgysdid

insert into #t select *from #tttt

truncate table #tttt

insert into @t ( ddid, fhdid, ckdid, zgysdid, cwysdid )select *from #t

drop table #t

drop table #tt

drop table #ttt

drop table #tttt

select *from @t

end

----執行exec  proc_xsddgz_lj