proc_number_his.sql

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

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

Create procedure Proc_Number_His
@who_used varchar(15)=NUll,
@identifier char(4) =null,
@frm_dt  datetime = null,
@end_dt  datetime = null

as
begin
	if (isnull(@who_used,'')<>'') and (isnull(@identifier,'')='')  
	begin
		select a.id as [Vendor ID],a.Vendor_nm as [Vendor Name],
		b.Order_Number as [Order Number],b.who_used as [Who Used],
		b.used_dt as [Used Date],
		case when b.reply =0 then 'N' else 'Y' end as Reply
		from PO_Number_List b,ord_mst a where b.order_number = a.pono 
		and b.who_used =@who_used 
		and b.used_dt between @frm_dt and @end_dt
		 order by  b.used_dt asc
	end
	
	else if(isnull(@identifier,'')<>'') and (isnull(@who_used,'')='')
	begin
		select a.id as [Vendor ID],a.Vendor_nm as [Vendor Name],
		b.Order_Number as [Order Number],b.who_used as [Who Used],
		b.used_dt as [Used Date],
		case when b.reply =0 then 'N' else 'Y' end as Reply
		from PO_Number_List b,ord_mst a where b.order_number = a.pono 
		and  b.order_number like @identifier+'%'
		and b.used_dt between @frm_dt and @end_dt
		 order by  b.used_dt asc	
	end
		else if(isnull(@identifier,'')<>'') and (isnull(@who_used,'')<>'')
	begin
		select a.id as [Vendor ID],a.Vendor_nm as [Vendor Name],
		b.Order_Number as [Order Number],b.who_used as [Who Used],
		b.used_dt as [Used Date],
		case when b.reply =0 then 'N' else 'Y' end as Reply
		from PO_Number_List b,ord_mst a where b.order_number = a.pono 
		and  b.order_number   like @identifier+'%' and b.who_used =@who_used
		and b.used_dt between @frm_dt and @end_dt
		 order by  b.used_dt asc		
	end
	else
	 begin
		select a.id as [Vendor ID],a.Vendor_nm as [Vendor Name],
		b.Order_Number as [Order Number],b.who_used as [Who Used],
		b.used_dt as [Used Date],
		case when b.reply =0 then 'N' else 'Y' end as Reply
		from PO_Number_List b,ord_mst a where b.order_number = a.pono 
		and b.used_dt between @frm_dt and @end_dt
		 order by  b.used_dt asc	
       end
		
		
end

/*

	exec Proc_Number_His '邹伟锋','','2004-01-01','2005-03-17'

*/