AspNetPager与MSSQL海量分页存储过程结合

存储过程Pagination：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Pagination]
@tblName varchar(255), -- 表名
@PKName varchar(255), -- 字段名
@strGotFields varchar(1000) = '*' , --查询字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strSort varchar(255) = '', --排序字段
@strWhere varchar(2000) = '', -- 查询条件 (注意: 不要加 where)
@isCount bit = 1 --取得记录条数
AS
DECLARE @strSQL varchar(6000) -- 主语句
DECLARE @strTmp varchar(1000) -- 临时变量
DECLARE @strOrder varchar(500) -- 排序类型
DECLARE @strCount varchar(1000)
DECLARE @fldName varchar(255)
DECLARE @sortName varchar(255)
DECLARE @countSQL varchar(1000)
SET @fldName = @PKName
IF @strSort != ''
BEGIN
SET @sortName = @strSort
END
ELSE
BEGIN
SET @sortName = @PKName
END
IF @isCount = 1
BEGIN
IF @strWhere != ''
BEGIN
SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName + ' where ' + @strWhere
END
ELSE
BEGIN
SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName
END
EXEC (@countSQL)
RETURN
END
ELSE
BEGIN
IF @OrderType != 0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by ' + @sortName + ' desc'
END
ELSE
BEGIN
SET @strTmp = '>(select max'
SET @strOrder = ' order by ' + @sortName +' asc'
END
SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
IF @strWhere != ''
SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ')