proc_get_price_ver1.0.sql

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/*
 declare
  @ReturnID int 
exec   Proc_Get_Price 'V0000194','001099100100-CE004',8732,'2005-3-15',@ReturnID out

select * from po_price_mstr where hpn = '001099100100-CE004'
*/

ALTER  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
 cast(convert(varchar(50),effective_dt,111) as datetime)
 <=cast(convert(varchar(50),@delivery_dt,111) as datetime)
 
 and
          cast(convert(varchar(50),expire_dt,111) as datetime)
 >=cast(convert(varchar(50),@delivery_dt,111) as datetime)
 

 if(@@rowcount<=0)
 select @ErrorID  = -2
 else if(@@rowcount>1) 
 select @ErrorID = -3
 else
  select @ErrorID = 0
 end
 select @ErrorID 
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

---删除tb_new.down代码