proc_get_price.sql

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

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

Create procedure Proc_Get_Price
@vendor_id varchar(10) = null,
@hpn varchar(18)= null,
@item int = null,
@delivery_dt datetime = null,
@ErrorID   int output 
as
begin
	select @ErrorID = -1-- unknown error
	if not exists(select Item  from po_price_mstr
		where vendor_id =@vendor_id and  hpn = @hpn  and status ='A' and item = @item)
	select @ErrorID =-2 --Price not exists
	else
	begin
	select Item,Line,HPN,MOQ,Unit_Price,effective_dt,expire_dt as Expiration_dt from po_price_mstr  
	where  vendor_id =@vendor_id and hpn = @hpn  and status ='A' and item = @item
	and (datepart(yy,effective_dt)<=datepart(yy,@delivery_dt)
	and datepart(mm,effective_dt)<=datepart(mm,@delivery_dt)
	and datepart(dd,effective_dt)<=datepart(dd,@delivery_dt))
	and (datepart(yy,expire_dt)>=datepart(yy,@delivery_dt)
	and datepart(mm,expire_dt)>=datepart(mm,@delivery_dt)
	and datepart(dd,expire_dt)>=datepart(dd,@delivery_dt))
	if(@@rowcount<=0)
	select @ErrorID	 = -2
	else if(@@rowcount>1) 
	select @ErrorID = -3
	else
		select @ErrorID = 0
	end
	select @ErrorID 
end
go
 
/*

declare
@ErrorID int
exec proc_Get_Price 'V000058','940798000000',10,'2004-11-30',@ErrorID output

 select Line,HPN,Unit_Price,Effective_dt,expire_dt from  po_price_mstr where item =10
*/