SqlServer 分页的存储过程
--方法一 取数据30-40 先取前40 再取前10且不存在前30中
<pre name="code" class="sql"><p>if exists(select 1 from sysobjects where name='P_GetUserInfoByPage' and type='p')
drop proc P_GetUserInfoByPage//检查是否存在命名存储过程，存在则删除
go
create proc P_GetUserInfoByPage
(
@pageIndex int,--分页的索引，默认为1（查看的页数）
@pageSize int ,--分页的大小,每一页的数据条数
@pageCount int out --总的分页数
)
as
declare @endNum int, ---数据截止量
@sql varchar(500),--命令sql语句的长度
@recordCount int--总的数据条数
select @recordCount=COUNT(*)from tb_userinfo
--set @pageCount =ceiling(@recordCount *1/@pageSize )--
set @pageCount =ceiling (@recordCount*1.0/@pageSize )--ceiling进一法取值 必须将其精度提高到0.1
if(@pageIndex>@pageCount)
set @pageIndex=@pageCount
set @endNum=(@pageIndex -1)*@pageSize--查看的页数的前一页的最后一条数据
set @sql='select top '+convert(varchar(20),@pageSize)+' * from tb_userinfo where userid
not in(select top '+convert(varchar(20),@endNum)+'userid from tb_userinfo)'
exec (@sql)
go
declare @pageCount int
exec P_GetUserInfoByPage 3,3,@pageCount out
print @pageCount
select top 6* from tb_userinfo where userid
not in(select top 12 userid from tb_userinfo)
go
--SQL SERVER 2008 无法启动T-SQL调试
--exec sp_addsrvrolemember 'SDWM-20150327QS\Administrator', 'sysadmin'
--分页方法2
--开窗函数 取30-40 先对所有的数据进行排序 再取30-40
if exists(select * from sysobjects where name='P_GetUserInfoListByPage' and type='p')
drop proc P_GetUserInfoListByPage
go
create proc P_GetUserInfoListByPage
(
@pageIndex int,
@pageSize int
)
as
declare @statrNum int,
@eneNum int
set @statrNum=(@pageIndex-1)*@pageSize+1
set @eneNum=@pageIndex*@pageSize
select * from(select ROW_NUMBER () Over(order by userid) as rownum, * from tb_userinfo )as temp
where temp.rownum between @statrNum and @eneNum
go
exec P_GetUserInfoListByPage 2,6
exec P_GetUserInfoByPage 2,6
-----带条件的查询
if exists(select * from sysobjects where name='P_getListUserInfoByCondition' and type ='p')
drop proc P_getListUserInfoByCondition
go
create proc P_getListUserInfoByCondition
( @userid int,
@username varchar(30),
@roleid int,
@pageIndex int,
@PageSize int,
@pageCount int out
)
as
declare @recordCount int,--表示满足条件的数据总数
--拼接查询字符串
@sql nvarchar(3000),
@sqlconditional nvarchar(1000)
set @sqlconditional='';
set @sql ='select @rowcount=count(*) from tb_userinfo where 1=1';--查询语句 1=1格式匹配where
if @userid is not null and @userid >0
set @sqlconditional =@sqlconditional+ 'and userid='+CONVERT (nvarchar(10),@userid)+'';
if @username is not null and @username <> N''--N Unicode格式字符匹配
set @sqlconditional=@sqlconditional+ 'and username like''%'+@username+'%'' ';
if @roleid is not null and @roleid >0
set @sqlconditional=@sqlconditional+'and roleid ='+CONVERT (nvarchar(20),@roleid)+'';
set @sql=@sql +@sqlconditional
exec sp_executesql @sql,N'@rowcount int out',@recordCount out--sp_executesql
set @pageCount=CEILING (@recordCount *1.0/@PageSize);
declare
@statrNum int,
@endNum int,
@sqlpage nvarchar(4000)
set @statrNum=(@pageIndex-1)*@pageSize+1;
set @endNum=@pageIndex*@pageSize
set @sqlpage =' Select ROW_NUMBER() over(order by userid) as rcount,* From tb_UserInfo Where 1=1 ';
set @sqlpage = @sqlpage + @sqlconditional;--把sql拼接的查询条件组合在一起
set @sqlpage = 'Select * From ('+@sqlpage+') as t where t.rcount between '+convert(varchar(20),@statrNum)+
' and '+convert(varchar(20),@endNum)+' ';
exec sp_executesql @sqlpage
go
declare @pageCount int
exec P_getListUserInfoByCondition null,'a',null,1,100,@pageCount out
print @pageCount</p><h2>--通用存储分页（开窗函数）</h2>if exists (select 1
<span style="white-space:pre"></span> from sysobjects
<span style="white-space:pre"></span> where [name]='proc_CommonPage'
<span style="white-space:pre"></span> and [type]='p')
drop proc proc_CommonPage
go
create proc proc_CommonPage
( @pageSize int,--分页大小（每一页多少）
@pageIndex int,--分页索引（页数）
@tablename varchar(50),--表名
@prikey varchar(20),--查询关键字
@condtional varchar(2000),--查询条件
@pageCount int out --总页数
)
as
--构建查询的sql语句
declare @sql nvarchar(1000);--查询的sql
declare @recordCount int,--数据总数
@startIndex int,--分页的启示行
<span style="white-space:pre"></span>@endIndex int--分页的结束行
set @sql='select @recordCount=count(*) from '+@tablename+' where 1=1 ';
if<span style="white-space:pre"></span>@condtional is not null and @condtional<> N''--查询条件不为空 也不为空格
begin
set @sql=@sql+@condtional--把查询条件加入sql语句
end
print @sql
exec sp_executesql @sql,N'@recordCount int out',@recordCount out;
--计算最大的分页数--
set @pageCount = CEILING(@recordcount*1.0/@pagesize);--最大的分页数 进一法取值
if @pageindex > @pageCount --如果分页的索引大于最大的索引
begin
set @pageindex = @pageCount;
end
--构建分页的查询的起始行和结束行
set @startIndex = (@pageindex-1)*@pagesize +1;--计算起始行
set @endIndex = @pageindex *@pagesize
--构建分页的sql
set @sql =''
set @sql = 'Select row_number() over (order by '+@prikey+' asc) as rowid,* from '+@tablename+' where 1=1';
if @condtional is not null and @condtional <> N'' --如果查询条件不为空
begin
set @sql = @sql+ @condtional;
end
set @sql = 'Select * From ('+@sql+') as tc where tc.rowid between '+convert(varchar(5),@startIndex)+' and '+convert(varchar(5),@endIndex)+'';
exec (@sql)--查询语句
go
--测试
declare @pagecount int
exec proc_CommonPage 1,5,'tb_userinfo','userid','And usertrue=''aa'' and roleid=2',@pagecount out
go
declare @recordCount int
select @recordCount=count(*) from tb_userinfo where 1=1
pt
drop proc P_GetUserInfoByPage//检查是否存在命名存储过程，存在则删除
go
create proc P_GetUserInfoByPage
(
@pageIndex int,--分页的索引，默认为1（查看的页数）
@pageSize int ,--分页的大小,每一页的数据条数
@pageCount int out --总的分页数
)
as
declare @endNum int, ---数据截止量
@sql varchar(500),--命令sql语句的长度
@recordCount int--总的数据条数
select @recordCount=COUNT(*)from tb_userinfo
--set @pageCount =ceiling(@recordCount *1/@pageSize )--
set @pageCount =ceiling (@recordCount*1.0/@pageSize )--ceiling进一法取值 必须将其精度提高到0.1
if(@pageIndex>@pageCount)
set @pageIndex=@pageCount
set @endNum=(@pageIndex -1)*@pageSize--查看的页数的前一页的最后一条数据
set @sql='select top '+convert(varchar(20),@pageSize)+' * from tb_userinfo where userid
not in(select top '+convert(varchar(20),@endNum)+'userid from tb_userinfo)'
exec (@sql)
go
declare @pageCount int
exec P_GetUserInfoByPage 3,3,@pageCount out
print @pageCount
select top 6* from tb_userinfo where userid
not in(select top 12 userid from tb_userinfo)
go
--SQL SERVER 2008 无法启动T-SQL调试
--exec sp_addsrvrolemember 'SDWM-20150327QS\Administrator', 'sysadmin'
--分页方法2
--开窗函数 取30-40 先对所有的数据进行排序 再取30-40
if exists(select * from sysobjects where name='P_GetUserInfoListByPage' and type='p')
drop proc P_GetUserInfoListByPage
go
create proc P_GetUserInfoListByPage
(
@pageIndex int,
@pageSize int
)
as
declare @statrNum int,
@eneNum int
set @statrNum=(@pageIndex-1)*@pageSize+1
set @eneNum=@pageIndex*@pageSize
select * from(select ROW_NUMBER () Over(order by userid) as rownum, * from tb_userinfo )as temp
where temp.rownum between @statrNum and @eneNum
go
exec P_GetUserInfoListByPage 2,6
exec P_GetUserInfoByPage 2,6
-----带条件的查询
if exists(select * from sysobjects where name='P_getListUserInfoByCondition' and type ='p')
drop proc P_getListUserInfoByCondition
go
create proc P_getListUserInfoByCondition
( @userid int,
@username varchar(30),
@roleid int,
@pageIndex int,
@PageSize int,
@pageCount int out
)
as
declare @recordCount int,--表示满足条件的数据总数
--拼接查询字符串
@sql nvarchar(3000),
@sqlconditional nvarchar(1000)
set @sqlconditional='';
set @sql ='select @rowcount=count(*) from tb_userinfo where 1=1';--查询语句 1=1格式匹配where
if @userid is not null and @userid >0
set @sqlconditional =@sqlconditional+ 'and userid='+CONVERT (nvarchar(10),@userid)+'';
if @username is not null and @username <> N''--N Unicode格式字符匹配
set @sqlconditional=@sqlconditional+ 'and username like''%'+@username+'%'' ';
if @roleid is not null and @roleid >0
set @sqlconditional=@sqlconditional+'and roleid ='+CONVERT (nvarchar(20),@roleid)+'';
set @sql=@sql +@sqlconditional
exec sp_executesql @sql,N'@rowcount int out',@recordCount out--sp_executesql
set @pageCount=CEILING (@recordCount *1.0/@PageSize);
declare
@statrNum int,
@endNum int,
@sqlpage nvarchar(4000)
set @statrNum=(@pageIndex-1)*@pageSize+1;
set @endNum=@pageIndex*@pageSize
set @sqlpage =' Select ROW_NUMBER() over(order by userid) as rcount,* From tb_UserInfo Where 1=1 ';
set @sqlpage = @sqlpage + @sqlconditional;--把sql拼接的查询条件组合在一起
set @sqlpage = 'Select * From ('+@sqlpage+') as t where t.rcount between '+convert(varchar(20),@statrNum)+
' and '+convert(varchar(20),@endNum)+' ';
exec sp_executesql @sqlpage
go
declare @pageCount int
exec P_getListUserInfoByCondition null,'a',null,1,100,@pageCount out
print @pageCount</p><h2>--通用存储分页（开窗函数）</h2>if exists (select 1
<span style="white-space:pre"></span> from sysobjects
<span style="white-space:pre"></span> where [name]='proc_CommonPage'
<span style="white-space:pre"></span> and [type]='p')
drop proc proc_CommonPage
go
create proc proc_CommonPage
( @pageSize int,--分页大小（每一页多少）
@pageIndex int,--分页索引（页数）
@tablename varchar(50),--表名
@prikey varchar(20),--查询关键字
@condtional varchar(2000),--查询条件
@pageCount int out --总页数
)
as
--构建查询的sql语句
declare @sql nvarchar(1000);--查询的sql
declare @recordCount int,--数据总数
@startIndex int,--分页的启示行
<span style="white-space:pre"></span>@endIndex int--分页的结束行
set @sql='select @recordCount=count(*) from '+@tablename+' where 1=1 ';
if<span style="white-space:pre"></span>@condtional is not null and @condtional<> N''--查询条件不为空 也不为空格
begin
set @sql=@sql+@condtional--把查询条件加入sql语句
end
print @sql
exec sp_executesql @sql,N'@recordCount int out',@recordCount out;
--计算最大的分页数--
set @pageCount = CEILING(@recordcount*1.0/@pagesize);--最大的分页数 进一法取值
if @pageindex > @pageCount --如果分页的索引大于最大的索引
begin
set @pageindex = @pageCount;
end
--构建分页的查询的起始行和结束行
set @startIndex = (@pageindex-1)*@pagesize +1;--计算起始行
set @endIndex = @pageindex *@pagesize
--构建分页的sql
set @sql =''
set @sql = 'Select row_number() over (order by '+@prikey+' asc) as rowid,* from '+@tablename+' where 1=1';
if @condtional is not null and @condtional <> N'' --如果查询条件不为空
begin
set @sql = @sql+ @condtional;
end
set @sql = 'Select * From ('+@sql+') as tc where tc.rowid between '+convert(varchar(5),@startIndex)+' and '+convert(varchar(5),@endIndex)+'';
exec (@sql)--查询语句
go
--测试
declare @pagecount int
exec proc_CommonPage 1,5,'tb_userinfo','userid','And usertrue=''aa'' and roleid=2',@pagecount out
go
declare @recordCount int
select @recordCount=count(*) from tb_userinfo where 1=1
pt