proc_rfq_hdr_upd.sql

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

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

Create procedure Proc_RFQhdr_upd
@action_cd  char(1) = null,
@RFQ_Number  varchar(12) =null output,
@RFQ_TYPE    char(4) = null,
@Vendor_ID   varchar(10) = null,
@Vendor_NM   varchar(120) = null,
@DeadLine    datetime = null,
@Customer_ID varchar(5) = null,
@Customer_nm varchar(120) = null,
@Model_number     varchar(30) = null,
@model_nm     varchar(120)= null,
@Remark	      varchar(120)=null,
@Creation_dt  datetime = null,
@Created_by   varchar(15)= null,
@Update_dt    datetime = null,
@Updated_by   varchar(15) = null,
@Return_ID    int = null output
as
begin
    select @Return_ID = 0 -- normal successully
    if(@action_cd = 'N')
     begin
		
     begin tran
  	exec Proc_Get_RFQ_Number 'Y',@RFQ_Number out
           insert into PO_RFQ_Hdr
(
RFQ_Number,
RFQ_TYPE,
Version,
Vendor_ID,
Vendor_NM,
DeadLine,
RFQ_Date,
Customer_ID,
Customer_nm,
Model_number,
Model_nm,
Remark,
Creation_dt,
Created_by,
Updated_by,
Update_dt
)
values
(
@RFQ_Number,
@RFQ_TYPE,
1,
@Vendor_ID,
@Vendor_NM,
@DeadLine,
getdate(),
@Customer_ID,
@Customer_nm,
@Model_number,
@model_nm,
@remark,
getdate(),
@Created_by,
@Updated_by,
getdate()
)
select @Return_ID = @@Error
select @RFQ_Number 

if(@@error<>0) 
 rollback tran
else
 commit tran
end

else if(@action_cd ='M') 
begin
	update PO_RFQ_Hdr
	set RFQ_TYPE=@RFQ_TYPE,
            Version=Version+1,
	    Vendor_ID=@Vendor_ID,
	    Vendor_NM=@Vendor_NM,
	    DeadLine=@DeadLine,
	    Customer_ID=@Customer_ID,
	    Customer_nm=@Customer_nm,
	    Model_number=@Model_number,
	    model_nm = @model_nm,
            remark = @remark,
	    Creation_dt=@Creation_dt,
	    Created_by=@Created_by,
	    Update_dt=getdate(),
	    Updated_by=@Updated_by
	where RFQ_Number = @RFQ_Number

	select @Return_ID = @@Error

	end
	else
		if(@action_cd = 'D') 
	begin
		begin tran
		delete from po_RFQ_Item 
		where RFQ_Number = @RFQ_Number 
		delete from PO_RFQ_Hdr where RFQ_Number=@RFQ_Number
		if(@@error<>0) 
		rollback tran
		else commit tran

	end
	
		select @Return_ID
end
	
go