proc_vndcontact_upd.sql

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

if object_id('dbo.Proc_Vndcontact_upd') is not null
drop procedure dbo.Proc_Vndcontact_upd

go

Create procedure Proc_Vndcontact_upd
@action_cd		CHAR(1) = null,
@Vendor_id	varchar(8) = null,
@Item		int = null,
@Last_nm	varchar(15) = null,
@First_nm	varchar(15) = null,
@Title		varchar(4) = null,
@Nickname	varchar(30) = null,
@Country	varchar(15) = null,
@Language	varchar(30) = null,
@Telephone	varchar(22) = null,
@Extension1	varchar(10) = null,
@Fax		varchar(22) = null,
@Extension2	varchar(10) = null,
@Mobile1	varchar(11) = null,
@Mobile2	varchar(11) = null,
@Email1		varchar(30) = null,
@Email2		varchar(30) = null,
@Priority	int = null,
@Status		char(1) = null,
@Follow_Buyer	varchar(15) = NULL,
@Created_By     varchar(15) = null,
@Updated_By 	varchar(15) = null,
@Return_code    int output
as
declare
	@Maxitem int 
begin
		if(upper(@action_cd) ='N') 
		begin
			if not exists(select vendor_id from po_vendor_mstr where vendor_id =@vendor_id)
			select @Return_Code = -2	--Vendor Not Exits
			else
			begin
				select @Maxitem = isnull(max(item),0)+1 from po_vndcontact_mstr where vendor_id =@vendor_id
				insert into po_vndcontact_mstr(
	      Vendor_id,
              item,
              Last_nm,
              First_nm,
              Title,
              Nickname,
              Country,
              Language,
              Telephone,
              Extension1,
              Fax,
              Extension2,
              Mobile1,
              Mobile2,
              Email1,
              Email2,
              Priority,
              Status,
              Follow_Buyer,
              Created_By,
              Creation_dt,
              Updated_By,
              Update_dt
	 ) values
	(
	     @Vendor_id,
             @MaxItem,
             @Last_nm,
             @First_nm,
             @Title,
             @Nickname,
             @Country,
             @Language,
             @Telephone,
             @Extension1,
             @Fax,
             @Extension2,
             @Mobile1,
             @Mobile2,
             @Email1,
             @Email2,
             @Priority,
             'A',
             @Follow_Buyer,
             @Created_By,
	     getdate(),
             @Updated_By,
             getdate()
	)
	 select @Return_code =@@error
	end
		select @Return_code
	end
	else if(upper(@action_cd) ='M')
	begin
		if not exists(select vendor_id from po_vndcontact_mstr where vendor_id = @vendor_id and item = @item)
		select @Return_code = -2 ---not exists 
	begin
	 update PO_vndcontact_mstr
	   set
		Last_nm      =   @Last_nm,
		First_nm      =   @First_nm,
		Title      =   @Title,
		Nickname      =   @Nickname,
		Country      =   @Country,
		Language      =   @Language,
		Telephone      =   @Telephone,
		Extension1      =   @Extension1,
		Fax      =   @Fax,
		Extension2      =   @Extension2,
		Mobile1      =   @Mobile1,
		Mobile2      =   @Mobile2,
		Email1      =   @Email1,
		Email2      =   @Email2,
		Priority      =   @Priority,
		Status      =   @Status,
		Follow_Buyer      =   @Follow_Buyer,
		Updated_by = @updated_by,
		Update_dt      =   getdate()
		where vendor_id = @vendor_id and item = @item
		
		select @Return_code =@@error
	end
		select @Return_code
	end
end