proc_mtlprice_get.sql

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

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

Create Procedure Proc_MtlPrice_Get
@hpn varchar(18) = null,
@cpn varchar(30) = null,
@mpn varchar(30) = null,
@mtl_nm varchar(120) = null,
@vendor_id varchar(10) = null
as
declare
   @s1 varchar(120),
   @s2 varchar(120),
   @s3 varchar(120),
   @s4 varchar(120),
   @s5 varchar(120),
   @gstr varchar(800),
   @sql nvarchar(4000)
begin
	if(isnull(@hpn,'')<>'') 
	select @s1 = ' and a.hpn like '+''''+@hpn+'%'+''''
	else
	select @s1 =''
	
	if(isnull(@cpn,'')<>'') 
	select @s2 = ' and a.cpn like '+''''+@cpn+'%'+''''
	else
	select @s2 = ''
	
	if(isnull(@mpn,'')<>'') 
	select @s3 =' and a.mpn  like '+''''+@mpn+'%'+''''
	else
	select @s3 = ''
	
	if(isnull(@mtl_nm,'')<>'') 
	select @s4 = ' and a.description like '+''''+@mtl_nm+'%'+''''
	else
	select @s4 = ''
	
	if(isnull(@vendor_id,'')<>'') 
	select @s5=' and a.vendor_id ='+''''+@vendor_id+''''
	else
	select @s5 =''
select *  into #tmp_PO_Price_Mstr from PO_Price_mstr
where isnull(hpn,'')<>''
order by hpn,update_dt 

select @gstr ='
select (select count(*) from  PO_Price_Mstr where 
hpn=a.hpn 
and itemseq<=a.itemseq ) as Item,a.hpn,a.CPN,a.MPN,a.Description,convert(numeric(18,8),a.Unit_Price) as Unit_Price,a.Currency,
a.MoQ,a.Effective_dt as [Effective Date],a.Expire_dt as [Expiration Date],
a.Basic_UoM as [Basic UoM],Vendor_UoM as [Vendor UoM],a.vendor_id as [Vendor ID],b.vendor_nm as [Vendor Name]
from #tmp_PO_Price_Mstr a ,PO_vendor_Mstr b where a.vendor_id =b.Vendor_ID'

select @sql=@gstr+@s1+@s2+@s3+@s4+@s5+' order by a.hpn '
exec(@sql)

drop table #tmp_PO_Price_Mstr

end


/*
select @gstr ='
select (select count(*) from  PO_Price_Mstr where 
isnull(hpn,'+''' '''+')<>'+''' '''+' and 
hpn=a.hpn 
and itemseq<=a.itemseq ) as Item,a.hpn,a.CPN,a.MPN,a.Description,convert(numeric(18,8),a.Unit_Price) as Unit_Price,a.Currency,
a.MoQ,a.Effective_dt,a.Expire_dt as Expiration_dt,
a.Basic_UoM,Vendor_UoM,a.vendor_id,b.vendor_nm

from #tmp_PO_Price_Mstr a ,PO_vendor_Mstr b where a.vendor_id =b.Vendor_ID
and isnull(a.hpn,'+''' '''+')<>'+''' '''
*/

-- Proc_MtlPrice_Get null,null,null,null,'V0000193'