代码如下:CREATE procedure STKCNTGEN1NEW as @date smalldatetime,@type char(1),@sctarget char(2),@flag int AS if @date='' or @type='' begin raiserror('資料不全!',11,-1) return endif( select count(*) from STKCNT where DATE=@date )=0 begin raiserror('請先存儲上層記錄!',11,-1) return end if( select count(*) from STKCNTDTL where DATE=@date )>0 begin raiserror('已經存在當天的記錄!',11,-1) return end delete from STKCNTDTL from STKCNTDTL S,STKCNT ST where ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE insert STKCNTDTL(DATE, LOC, PRONUM, QTY,TOTALQTY) select @date,S.LOC, S.PRONUM,S.PHYQTY,0 from STOCKNEW S,PRODUCT P,LOC L where S.LOC=L.LOC AND L.TYPE=1 AND S.PRONUM=P.PRONUM AND P.PROTYPE=case when @sctarget='00' then P.PROTYPE else @sctarget end ORDER BY S.LOC,S.PRONUMdeclare @number int declare locs scroll cursor for select distinct S.LOC from STKCNTDTL S,STKCNT ST WHERE ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE ORDER BY S.LOC declare @locs as char(5) open locs fetch from locs into @locs while @@fetch_status=0 begin select @number=1 declare ppp scroll cursor for select S. PRONUM from STKCNTDTL S,STKCNT ST where ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE and S.LOC=@locs ORDER BY S.LOC, S.PRONUM declare @pronum char(20) open ppp fetch from ppp into @pronum while @@fetch_status=0 begin declare @totalqty float select @totalqty=sum(QTY) from STKCNTDTL where PRONUM=@pronum AND DATE=@date if @flag=1 begin declare @TICKET char(11) declare @p varchar(4) select @p=convert(char,@number) while len(@p)<4 begin select @p='0'+@p end select @TICKET =rtrim(@locs)+ right(left(convert(char(10),@date,111),7),2)+@p update STKCNTDTL set TICKET =@TICKET,TOTALQTY=@totalqty where LOC=@locs and DATE=@date and PRONUM=@pronum select @number=@number+1 end else begin update STKCNTDTL set TOTALQTY=@totalqty where LOC=@locs and DATE=@date and PRONUM=@pronum end fetch next from ppp into @pronum end close ppp deallocate ppp fetch next from locs into @locs end close locs deallocate locs group by S.PRONUMGO
as
@date smalldatetime,@type char(1),@sctarget char(2),@flag int
AS
if @date='' or @type=''
begin
raiserror('資料不全!',11,-1)
return
endif( select count(*) from STKCNT where DATE=@date )=0
begin
raiserror('請先存儲上層記錄!',11,-1)
return
end
if( select count(*) from STKCNTDTL where DATE=@date )>0
begin
raiserror('已經存在當天的記錄!',11,-1)
return
end
delete from STKCNTDTL from STKCNTDTL S,STKCNT ST where ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE
insert STKCNTDTL(DATE, LOC, PRONUM, QTY,TOTALQTY)
select @date,S.LOC, S.PRONUM,S.PHYQTY,0
from STOCKNEW S,PRODUCT P,LOC L
where S.LOC=L.LOC AND
L.TYPE=1 AND
S.PRONUM=P.PRONUM AND
P.PROTYPE=case when @sctarget='00' then P.PROTYPE else @sctarget end
ORDER BY S.LOC,S.PRONUMdeclare @number int
declare locs scroll cursor for select distinct S.LOC from STKCNTDTL S,STKCNT ST WHERE ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE ORDER BY S.LOC
declare @locs as char(5)
open locs
fetch from locs into @locs
while @@fetch_status=0
begin
select @number=1
declare ppp scroll cursor for select S. PRONUM from STKCNTDTL S,STKCNT ST where ST.DATE=@date and ST.TYPE=@type and S.DATE=ST.DATE and S.LOC=@locs ORDER BY S.LOC, S.PRONUM
declare @pronum char(20)
open ppp
fetch from ppp into @pronum
while @@fetch_status=0
begin
declare @totalqty float
select @totalqty=sum(QTY) from STKCNTDTL where PRONUM=@pronum AND DATE=@date
if @flag=1
begin
declare @TICKET char(11)
declare @p varchar(4)
select @p=convert(char,@number)
while len(@p)<4
begin
select @p='0'+@p
end
select @TICKET =rtrim(@locs)+ right(left(convert(char(10),@date,111),7),2)+@p
update STKCNTDTL set TICKET =@TICKET,TOTALQTY=@totalqty where LOC=@locs and DATE=@date and PRONUM=@pronum
select @number=@number+1
end
else
begin
update STKCNTDTL set TOTALQTY=@totalqty where LOC=@locs and DATE=@date and PRONUM=@pronum
end
fetch next from ppp into @pronum
end
close ppp
deallocate ppp
fetch next from locs into @locs
end
close locs
deallocate locs
group by S.PRONUMGO