Question

可以写出用多个条件实现模糊查询并且能分页的存储过程！！急！！！

CREATE proc UP_Tm_LeaveChildren_GetModels
(
@Ccorp varchar(30), --幼儿园
@Classname varchar(20), --班级名称
@Cname varchar(40), --幼儿姓名
@LeaveSDate varchar(30), --起始时间
@LeaveEDate varchar(30), --终止时间
@LeaveWhy varchar(20), --离园类型
@PageIndex int, --页码
@PageSize int --每页显示的数据条数
)
as
begin
declare @SqlText varchar(1024)
set @SqlText='select top ' +cast(@PageSize as varchar(12))+'Ccorp,Classname,Childid,Cname,Csex,convert(char(10),Cbirth,120) as Cbirth,
convert(char(10),InDate,120) as InDate,Address,Hphone,convert(char(10),LeaveDate,120) as LeaveDate,LeaveWhy
from (select * from Tm_LeaveChildren
where Ccorp ='+''''+@Ccorp+''''+'and Classname like '+''''+'%'+@Classname+'%'+''''+ 'and Cname like' +''''+ '%'+@Cname+'%' +''''+ 'and LeaveDate <= '+''''+convert(char(10),@LeaveSDate,120)+''''+'and LeaveDate <= '+''''+
convert(char(10),@LeaveEDate,120)+''''+'and LeaveWhy like'+''''+ '%'+@LeaveWhy+'%' +''''+')
Tm_LeaveChildren
where Childid
not in
(
select top '+cast((@PageIndex-1)*@PageSize as varchar(32)) +'Childid from (select * from Tm_LeaveChildren
where Ccorp ='+''''+@Ccorp+''''+'and Classname like ' +''''+ '%'+ @Classname+'%' +''''+ 'and Cname like'+''''+'%'+ @Cname +'%'+''''+ 'and LeaveDate <= '+''''+convert(char(10),@LeaveSDate,120)+''''+'
and LeaveDate <= '+''''+convert(char(10),@LeaveEDate,120)+''''+'and LeaveWhy like'+''''+ '%'+@LeaveWhy+'%'+'''' +')
Tm_LeaveChildren order by Childid desc
)
order by Childid desc'

exec(@SqlText)
end
GO

我写的存储过程不能实现模糊查询！该怎么修改啊！！

Answer 1

你可以先通过模糊查询把查询的结果放入临时表中
然后再对临时表进行操作
这样的话 结构很清晰 不容易出错
select * into #temp from Tm_LeaveChildren
where Ccorp ='+''''+@Ccorp+''''+'and Classname like '+''''+'%'+@Classname+'%'+''''+ 'and Cname like' +''''+ '%'+@Cname+'%' +''''+ 'and LeaveDate <= '+''''+convert(char(10),@LeaveSDate,120)+''''+'and LeaveDate <= '+''''+
convert(char(10),@LeaveEDate,120)+''''+'and LeaveWhy like'+''''+ '%'+@LeaveWhy+'%' +''''+')

select top @PageSize * from #temp where Childid
not in
( select top (@PageIndex-1)*@PageSize from #temp order by Childid desc
)
order by Childid desc
我没有验证上面的sql是否正确 你自己看看吧

Answer 2

/* 创建者雨 只需要把SQL语句,页码,每页显示的数量执行就可以*/
CREATE PROCEDURE [dbo].[les_AllowPaging]
@pageindex int,
@PageSize int,
@tsql varchar(4000)
as

Declare @AllowPagingSql varchar(4000)

set @AllowPagingSql=
'select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId, * FROM ( select *, 1 as orderbyID from ( '+@tsql +' ) as tbs1 )
as Tabl1
) as table2
where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
set @AllowPagingSql= replace( @AllowPagingSql,'$','''')
exec (@AllowPagingSql)

set @AllowPagingSql='select
case
when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'
when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1
end as pageCount,count(*) as RowsCount from ('+replace(@tsql,'$','''')+') as tab1'
exec (@AllowPagingSql)
/*
exec [les_AllowPaging] 15,100,' select * from sys.sysobjects '
exec [les_AllowPaging]%2