sql server 存储过程 实现模糊查询

 --彩票卡详细信息

if exists( select * from sysobjects where name = 'CP_Card_BatchDetail')
begin
 drop procedure CP_Card_BatchDetail
end
go
create procedure CP_Card_BatchDetail
@BatchNo nvarchar(50),
@cardtype nvarchar(50),
@stateDesc nvarchar(30),
@begDate datetime,
@endDate datetime

as
begin

select a.BatchNo, a.money cardType,a.createDate,b.stateDesc, a.Number totalNum
,(select count(id) from cpCard_info where cardstate=2 and batchno=a.batchno) useCount,
(a.Number-(select count(id) from cpCard_info where cardstate=2 and batchno=a.batchno)) lessCount,
(select count(id) from cpCard_info where cardstate=4 and batchno=a.batchno) zuxiaoCount,
a.enddate,(case when datediff(dd,a.endDate,getdate())>0 then '进行中' else '已完成' end) xiaoshouCase
from CPCardBatch_Info a
left join CPCard_State b on a.BatchState=b.state

where batchno like '%'+rtrim(@BatchNo)+'%' and
money like '%'+rtrim(@cardtype)+'%' and
stateDesc like '%'+rtrim(@stateDesc)+'%' and
datediff(dd,@begDate,createdate)>=0 and
datediff(dd,createdate,@endDate)>=0
end

--exec CP_Card_BatchDetail '','','','2006-10-1','2008-10-10'