proc_po_price_list.sql

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

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

create Procedure Proc_PO_Price_List
@vendor_id        varchar(10) = null,
@from_dt	  datetime   = null,
@end_dt           datetime   = null
as
begin
	declare @Csql varchar(8000)
	declare @i int,
	@Creation_dt varchar(50) 
	set @i=1
	set @csql='select b.Vendor_nm,a.HPN,a.Unit_Price '

	declare #cur1 cursor for 
	select distinct convert(varchar(50),a.Creation_dt,111) as Creation_dt  from viw_Price_history a,po_information_record b
	where a.hpn = b.hpn  
	and con
       -- group by hpn  order by convert(varchar(19),Creation_dt,102)

	open #cur1
	fetch next from #cur1 into @Creation_dt
	while @@fetch_status=0
	begin
		set @csql=@csql+',sum(case when Creation_dt='''+@Creation_dt+'''
		 then Unit_Price else 0 end) as ['+@Creation_dt+']' 
		fetch next from #cur1 into @Creation_dt
	end
	close #cur1
	deallocate #cur1
	set @csql=@csql+'  from viw_Price_History a,po_information_Record b where a.hpn = b.hpn   
		group by a.hpn,b.vendor_nm,a.unit_price order by HPN'

	print @csql
	-- exec(@csql)

end