?? 附件銷售存儲過程式.sql
字號:
declare @act int--操作類型1增加2修改3刪除
declare @pjzl char(5)--配件資料
declare @gt char(4)--柜臺號資料
declare @num int--出售的數量
declare @mon money--出售價格
declare @date datetime--日期
declare @person char(4)-- 售貨員
declare @fxlx char(1)--附件銷售類型
declare @memo char(200)--備注
declare @fjxcid char(12)--附件銷售ID
declare @OutNumber char(12) --output
declare @NowNO int
declare @ErrorMsg nvarchar(200)
declare @IDType char(2)
declare @numb int
declare @curfid char(12)
declare @curfnum int
declare @curfuse int
declare @curi int
if @act=1
begin
set @IDType='FX'
set @numb=4
set @curi=@num
DECLARE cur_fjrc SCROLL CURSOR FOR
select fid,fnum,fuse from tfjrc where fpjzl=@pjzl and fnum>fuse order by fdate
OPEN cur_fjrc
FETCH first FROM cur_fjrc
into @curfid,@curfnum,@curfuse
WHILE (@@FETCH_STATUS = 0)and (@curi>0)
BEGIN
if (@curfnum-@curfuse)>=@curi --此記錄恰好可以滿足申請進價的記錄
begin
set @num=@curi
set @curi=0
end
if (@curfnum-@curfuse)<@curi
begin
set @num=@curfnum-@curfuse
set @curi=@curi-@num--還有這么多需要進行申請進價
end --更新此條進貨記錄的FUSE的數目
update tfjrc set fuse=fuse+@num where fid=@curfid
exec pGetID @IDType,@numb,@OutNumber Output
if @@Error<>0
Goto Failed
insert into tfjxc(fid,fpjzl,fnum,fdate,fperson,ffxlx,fmemo,fmon,ffjrc)values(@OutNumber,@pjzl ,@num,@date ,@person,@fxlx,@memo,@mon,@curfid )
if @@Error<>0
Goto Failed
FETCH NEXT FROM cur_fjrc
into @curfid,@curfnum,@curfuse
END
CLOSE cur_fjrc
DEALLOCATE cur_fjrc
end
if @act=2
begin
update tfjxc set fgt=@gt,fperson=@person,ffxlx=@fxlx,fmemo=@memo,fmon=@mon where fid=@fjxcid
if @@Error<>0
Goto Failed
end
if @act=3
begin
set @curfid=(select fid from tfjxc where fid=@fjxcid)
set @curfnum=(select fnum from tfjxc where fid=@fjxcid)
update tfjrc set fuse=fuse-@curfnum where fid=@curfid
delete from tfjxc where fid=@fjxcid
end
Failed:
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -