【SQL开源代码栏目提醒】:网学会员SQL开源代码为您提供GoodsUP.sql参考,解决您在GoodsUP.sql学习中工作中的难题,参考学习。
use BuyGoods
go
if object_id('dbo.GoodsUP') is not null
drop procedure dbo.GoodsUP
go
create procedure dbo.GoodsUP
@action_cd char(1)=null,
@item int=null,
@Rec_date datetime=null,
@ckdate datetime=null,
@RPdate datetime=null,
@RPNO varchar(15)=null,
@Customer varchar(30)=null,
@vendor varchar(30)=null,
@source varchar(30)=null,
@model varchar(30)=null,
@custpn varchar(30)=null,
@type varchar(10)=null,
@description varchar(80)=null,
@bom varchar(80)=null,
@actual varchar(80)=null,
@Rec_qty numeric(12,2)=null,
@insp_qty numeric(12,2)=null,
@rej_qty numeric(12,2)=null,
@status char(2)=null,
@disposition varchar(15)=null,
@remarks varchar(125)=null,
@ckyear int=null,
@ckmonth int=null,
@ckday int=null
as
if(@action_cd='N')
begin
if not exists(select * from InGoods where item=@item and ckyear=@ckyear)
insert into InGoods(
item,rec_date,ckdate,rpdate,daysiniqc,RPno,customer,
vendor, source, model,custpn,type,description,
bom,actual,rec_qty,insp_qty,rej_qty,status,disposition,
remarks,ckyear, ckmonth,ckday,insp_date)
values(
@item,@rec_date,@ckdate,@rpdate,convert(int,@rpdate-@ckdate),
@RPno,@customer,@vendor,@source,@model, @custpn,@type,
@description,@bom,@actual,@rec_qty,@insp_qty,@rej_qty,
@status,@disposition,@remarks,@ckyear,@ckmonth, @ckday,
convert(char(4),@ckyear)+'/'+rtrim(convert(char(2),@ckmonth))+'/'+rtrim(convert(char(2),@ckday)))
if(@@error<>0)
print('插入新记录错误!')
return
end
if(@action_cd='M')
begin
update InGoods
set rec_date=@rec_date,ckdate=@ckdate,rpdate=@rpdate,daysiniqc=convert(int,@rpdate-ckdate),
Rpno=@Rpno,customer=@customer,vendor=@vendor,source=@source,model=@model,
custpn=@custpn, type=@type,description=@description,bom=@bom,
actual=@actual, rec_qty=@rec_qty,insp_qty=@insp_qty,rej_qty=@rej_qty,
status=@status,disposition=@disposition,remarks=@remarks,@ckmonth=@ckmonth
where item=@item and ckyear=@ckyear
if(@@error<>0)
print('更新错误!')
return
end
if(@action_cd='D')
begin
if exists(select * from InGoods where item=@item and ckyear=@ckyear)
delete from InGoods
where item=@item and ckyear=@ckyear
if(@@error<>0)
print('记录删除错误!')
return
end
go