- 博客(0)
- 资源 (3)
- 收藏
- 关注
RSS订阅高效的通用分页存储过程
-- Author: Curllion Zhang
-- Create date: 2010-1-11
-- Description: 分页查询
-- =============================================
CREATE PROCEDURE [dbo].[paging]
@tblName nvarchar(4000), ----要显示的表或多个表的连接
@fldName nvarchar(4000) = '*', ----要显示的字段列表
@pageSize int, ----每页显示的记录个数
@page int, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@strCondition nvarchar(2000) = '', ----查询条件,不需where
@strSort nvarchar(2000) --排序字段
AS
BEGIN
Declare @strTmp nvarchar(4000) ----存放取得查询结果总数的查询语句
--获取总条数
if @strCondition = ''
set @strTmp = ' select @Counts = count(*) from ' + @tblName
else
set @strTmp = ' select @Counts = count(*) from ' + @tblName + ' where ' + @strCondition
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
--计算@pageCount的值
if @Counts <= @pageSize
set @pageCount = 1
else
begin
if @counts % @pagesize = 0
set @pageCount = (@Counts / @pageSize)
else
set @pageCount = (@Counts / @pageSize) + 1
end
--检查@page的值
if @page > @pagecount
set @page = @pagecount
if @page < 1
set @page = 1
if @strCondition = ''
begin
set @strTmp = 'select * from (' +
'select row_number() over (order by ' + @strSort + ') as RowNum ,' + @fldName +
' from ' + @tblName +
') a where a.RowNum > ' + cast((@page -1) * @pageSize as varchar(128)) +
' and a.RowNum <= ' + cast(@page * @pageSize as varchar(128))
end
else
begin
set @strTmp = 'select * from (' +
'select row_number() over (order by ' + @strSort + ') as RowNum ,' + @fldName +
' from ' + @tblName + ' where ' + @strCondition +
') a where a.RowNum > ' + cast((@page -1) * @pageSize as varchar(128)) +
' and a.RowNum <= ' + cast(@page * @pageSize as varchar(128))
end
exec sp_executesql @strTmp
END
2010-08-29
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人