[导入]存储过程片段5

ALTER PROC [dbo].[FM_Business_Search]
@Business nvarchar(200), 
@ReceiveState nvarchar(10),
@BusinessDate1 datetime,
@BusinessDate2 datetime   
AS
declare @sql   NVARCHAR(4000)
declare @Time1 varchar(50)
declare @Time2 varchar(50)
begin
set @Time1=@BusinessDate1
set @Time2=@BusinessDate2
set @sql=' 
select Number,a.BusinessID, Enterprise as et,BusinessType,ReceiveState,convert(nvarchar,BusinessDate,23) as BusinessDateC           
from
FM_BusinessInfo as a left join FM_BusinessType on a.BusinessTypeID=FM_BusinessType.BusinessTypeID

where
not exists(select BusinessID, Enterprise as et,Number,BusinessTypeID,ReceiveState,convert(nvarchar,BusinessDate,23) as BusinessDateC
from FM_BusinessInfo as b where b.BusinessID=a.BusinessID and b.Business>a.Business)
and BusinessDate>='''+@Time1+'''
And BusinessDate<='''+@Time2+'''  '

if(LEN(@Business)>0)
begin
     set @sql=@sql+' and BusinessID like''%'+@Business+'%'''

end
if(LEN(@ReceiveState)>0)
begin
     set @sql=@sql+' and ReceiveState like''%'+@ReceiveState+'%'''
     set @sql=@sql+' order by Number desc'
end

end
exec(@sql)

文章来源:http://blog.163.com/kwanchan@yeah/blog/static/85538614200862081321513