一个SQLServer通用分页查询过程

存储过程内部原理：

1 先查询返回符合条件的总的记录数，动态计算页面上的显示信息，如符合条件的总记录数，总页数；

2 然后基于健值字段上去做not in的分页查询，性能上还是比较高的；

3 后面给出一个T-SQL的调用示例。

优点：一劳永逸，表名动态、条件动态、分组动态；

/*
@strTable --需要查询的表名或视图名
@strKeyField --关键字段(一般为表的主键字段)
@strFields --返回的字段列表
@strWhere --查询条件(不要加where)
@strOrderBy --排序语句
@strGroupBy --分组查询
@ilPageIndex --所要查询的页数
@ilPageSize --页大小
@ilRecordCount  --符条件的记录数
@ilPageCount --符合条件的总页数
*/
CREATE PROCEDURE [dbo].[up_page_select]
@strTable varchar(2048),
@strKeyField varchar(512),
@strFields varchar(2048),
@strWhere varchar(2048),
@strOrderBy varchar(2048),
@strGroupBy varchar(2048),
@ilPageIndex int,
@ilPageSize int = 20,
@ilRecordCount int OUTPUT,
@ilPageCount int OUTPUT
--WITH ENCRYPTION
AS
--
-- 分页查询数据
-- 要查询的表中，必须存在一个唯一键，否则无法使用此过程
--
DECLARE @SQL nvarchar(4000), @strFromCluse nvarchar(4000),
@strWhereCluse nvarchar(4000), @strOtherCluse nvarchar(4000)

SET @strFromCluse = ' FROM ' + @strTable

IF (@strWhere IS NOT NULL) AND (@strWhere <> '')
SET @strWhereCluse = ' WHERE ' + @strWhere
ELSE
SET @strWhereCluse = ''

SET @strOtherCluse = ''
IF (@strGroupBy IS NOT NULL) AND (@strGroupBy <> '')
SET @strOtherCluse = @strOtherCluse + ' GROUP BY ' + @strGroupBy


IF (@strOrderBy IS NOT NULL) AND (@strOrderBy <> '')
SET @strOtherCluse = @strOtherCluse + ' ORDER BY ' + @strOrderBy

IF (@strFields IS NULL) OR (@strFields = '')
SET @strFields = '*'

IF (@ilPageIndex <= 0)
SET @ilPageIndex = 1

IF @ilPageSize > 0
BEGIN
DECLARE @cur CURSOR, @keyvalue varchar(100), @i int


--返回符合条件的记录条数
 set @SQL = 'SELECT @sizec=COUNT('+ @strKeyField+')'+ @strFromCluse + @strWhereCluse
EXECUTE sp_executesql @SQL,N'@sizec as int output',@ilRecordCount output

--获取命中条数
SET @ilPageCount = CEILING(@ilRecordCount * 1.0 / @ilPageSize)

DECLARE @KeyList varchar(2000)

SET @i = (@ilPageIndex - 1) * @ilPageSize + 1
--返回符合条件的条件语句
set @KeyList= @strKeyField + 
' IN(SELECT top '+
convert(varchar,@ilPageSize*@ilPageIndex)+
' '+@strKeyField+@strFromCluse+
' where ' +@strKeyField+ 
' not in(select top '+
convert(varchar,@ilPageSize*(@ilPageIndex-1))+
' '+@strKeyField + @strFromCluse+' order by ' +
@strKeyField+ ' desc) order by ' +@strKeyField+ ' desc) '

IF (@strWhereCluse = '')
SET @strWhereCluse = ' WHERE ' + @KeyList
ELSE
SET @strWhereCluse = @strWhereCluse + ' AND ' + @KeyList


SET @SQL = 'SELECT ' + @strFields + @strFromCluse + @strWhereCluse + @strOtherCluse
END
ELSE
SET @SQL = 'SELECT ' + @strFields + @strFromCluse + @strWhereCluse + @strOtherCluse
--print @SQL
EXECUTE sp_executesql @SQL

--调用示例

DECLARE @return_value int,
@ilRecordCount int,
@ilPageCount int

EXEC @return_value = [dbo].[up_page_select]
@strTable = N'vi_member_list',
@strKeyField = N'member_id',
@strFields = N'*',
@strWhere = N'top_area=1 AND ( m_class in (1,2,3,4,5))',
@strOrderBy = N'm_create_datetime desc ',
@strGroupBy = '',
@ilPageIndex = 3,
@ilPageSize = 30,
@ilRecordCount = @ilRecordCount OUTPUT,
@ilPageCount = @ilPageCount OUTPUT


SELECT @ilRecordCount as N'@ilRecordCount',
@ilPageCount as N'@ilPageCount'


SELECT 'Return Value' = @return_value


GO