proc_get_rfq_number.sql

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

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


create     procedure Proc_Get_RFQ_Number
@flag        char(1) = null,    --------temporary or post to table Y/N
@Ord_num_out varchar(12) = null output
as
declare 
@Order_num varchar(12),
@Year char(2),
@maxitem int,
@temp_num varchar(4)
begin

	
	
	select @maxitem =  isnull(Number,1) from PO_RFQ_Number where IDEN='RFQ' 
	select @Year = isnull(right(convert(char(4),datepart(YY,getdate())),2),'05')
		select @temp_num = convert(char(4),@maxitem)
 	select @Order_num ='RFQ'+@year+'-'+ REPLICATE('0',5-len(@temp_num))+@temp_num
	select @Ord_num_out = @Order_num

    	if(upper(@flag) = 'Y') 
	begin
		
			update PO_RFQ_Number
				set Number = Number + 1,
		 		  last_number = @ord_num_out 
				 where IDEN='RFQ' 
			
		 	
				
	
	end
	select @Ord_num_out
end

go
/*
declare
@ord_number varchar(15)
exec Proc_Get_RFQ_Number 'N',@ord_number out

*/