利用存储过程实现分页 ，并且多条件查询

use shifenzheng
if exists(SELECT name FROM sysobjects WHERE name = 'sp_PageNumSelect1' AND type = 'P')
drop proc sp_PageNumSelect
go
create proc sp_PageNumSelect
(
@pageSize int,
@pageIndex int,
@name varchar(50),
@Mobile varchar(50),
@address varchar(50),
@zip varchar(6),
@Email varchar(50),
@start datetime,
@end datetime
)

as
declare @strSql varchar(2000) --主语句
declare @strCountSql varchar(2000) -- 总记录主语句
declare @strWhere varchar(100)
set @strWhere=''
--姓名
if @name<>''
begin
set @strWhere=@strWhere+'name='''+@name+''' and '
end
---手机
if @Mobile<>''
begin
set @strWhere=@strWhere+'Mobile='''+@Mobile+''' and '
end
--地址
if @address<>''
begin
set @strWhere=@strWhere+'address like('''+'%'+@address+'%'+''') and '
end
--邮编
if @zip<>''
begin
set @strWhere=@strWhere+'zip='''+@zip+''' and '
end
--邮箱
if @Email<>''
begin
set @strWhere=@strWhere+'Email='''+@Email+''' and '
end
--开始时间
if @Email<>''
begin
set @strWhere=@strWhere+'Version>='''+@start+''' and '
end
--结束时间
if @Email<>''
begin
set @strWhere=@strWhere+'Version<='''+@end+''' and '
end

if @strWhere<>''
Begin
set @strSql='select top ('+str(@pageSize)+') id,Name,ctfid,Gender,Birthday,[Address],Zip,Mobile,Tel,Fax,EMail,Nation,Education,
Company,[Version] from cdsgus0
where '+(@strWhere)+' id>isnull((select MAX(id) from cdsgus0 where id in(select top ('+str(@pageSize*(@pageIndex-1))+') id from cdsgus0 where '+@strWhere+' 1=1)),0)';


set @strCountSql = 'select count(id) from cdsgus0 where '+@strWhere+' 1=1';

End
else
Begin

set @strSql='select top '+str(@pageSize)+' id,Name,ctfid,Gender,Birthday,[Address],Zip,Mobile,Tel,Fax,EMail,Nation,Education,
Company,[Version] from cdsgus
where id>isnull((select MAX(id) from cdsgus where id in(select top ('+str(@pageSize*(@pageIndex-1))+') id from cdsgus)),0)'

set @strCountSql='select 20000000'
End

exec(@strSql);
exec(@strCountSql);
go


exec sp_PageNumSelect 20,1,'','','','','','',''

其中在sql中 ''，两个单引号是转义符号，转义成一个单引号

一般有变量时出现数据类型转换失败，一般都是sql语句拼接错误造成。将变量转换为字符换类型即可。

cast(@pageSize as varchar(50))

(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句

(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句

(利用SQL的游标存储过程分页)    效率最差，但是最为通用