高效的通用分页存储过程

-- 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