proc_vendor_mstr_upd.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.proc_Vendor_Mstr_Upd') is not null
drop procedure dbo.Proc_Vendor_Mstr_Upd
go

create procedure Proc_Vendor_Mstr_Upd
@Action_cd 		CHAR(1) = NULL,
@Vendor_ID		CHAR(8) = NULL,
@Vendor_class		char(1) = null,
@Vendor_nm		VARCHAR(250) = NULL,
@TEL			VARCHAR(80) = NULL,
@FAX			VARCHAR(80)= NULL,
@cont1			VARCHAR(80) = NULL,
@cont2			VARCHAR(80) = NULL,
@Address		VARCHAR(250) = NULL,
@payterms		VARCHAR(120) = NULL,
@Currency		CHAR(3) = NULL,
@Pay_Day		int = null,
@website		varchar(120) = null,
@email			varchar(120) = null,
@Remark			varchar(120) = null,
@created_by		varchar(15) = null,
@updated_by		varchar(15) = null
as
if(@action_cd='N') 
begin
declare 
@newitem int ,
@new_vendor_id char(8) 
select @newitem= isnull(max(item)+1,1) from PO_vendor_mstr
exec Proc_Get_Vendor_ID 'F',@new_vendor_id  output
begin
insert into PO_Vendor_Mstr(
	item,
	Vendor_id,
	Vendor_Class,
	vendor_nm,
	tel,
	fax,
	cont1,
	cont2,
	address,
	payterms,
	currency,
	pay_day,
	website,
	email,
	Remark,
	creation_dt,
	created_by,
	update_dt,
	updated_by
	)
values(
	@newitem,
	@new_vendor_id,
	@vendor_class,
	@vendor_nm,
	@tel,
	@fax,
	@cont1,
	@cont2,
	@address,
	@payterms,
	@currency,
	0,
	@website,
	@email,
	@Remark,
	getdate(),
	@created_by,
	getdate(),
	@updated_by
)

return 
end
end

if(@action_cd='M') 
begin
  update po_vendor_mstr 
	set
vendor_nm=@vendor_nm ,
vendor_class = @vendor_class,
tel=@tel,
fax=@fax,
cont1=@cont1,
cont2=@cont2,
address=@address,
payterms=@payterms,
currency = @currency,
website=@website,
email=@email,
update_dt=getdate(),
updated_by = @updated_by,
remark = @remark
where  vendor_id=@vendor_id
return 
end

if (@action_cd='D') 
begin
if exists(select * from po_vendor_mstr where  vendor_id = @vendor_id)
begin
if not exists(select ID from mat_mst where id = @vendor_id)
delete from po_vendor_mstr where  vendor_id=@vendor_id
else
print('cannot delete current vendor')
end
return
end