千万级分页存储过程

大家百度一下这个标题立马会出现很多相关信息，都大同小异，我自己拷贝的一个，应项目的需要，修改了一个排序的bug以及添加了返回总记录数，如下：SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--分页存储过程  CREATE PROCEDURE [dbo].[sp_Paging] (    @Tables nvarchar(1000),                --表名/视图名   @PrimaryKey nvarchar(100),             --主键   @Sort nvarchar(200) = NULL,            --排序字段(不带order by)   @pageindex int = 1,                    --当前页码   @PageSize int = 10,                    --每页记录数   @Fields nvarchar(1000) = N"*",         --输出字段   @Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)   @Group nvarchar(1000) = NULL,          --Group语句(不带Group By)   @TotalCount int OUTPUT                 --总记录数) AS   DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @operator char(2) DECLARE @type nvarchar(100) DECLARE @prec int 
--设定排序语句IF @Sort IS NULL OR @Sort = ""        SET @Sort = @PrimaryKey      IF CHARINDEX("DESC",@Sort)>0   BEGIN             SET @strSortColumn = REPLACE(@Sort, "DESC", "")             SET @operator = "<="     END ELSE     BEGIN                    SET @strSortColumn = REPLACE(@Sort, "ASC", "")                    SET @operator = ">="     END IF CHARINDEX(".", @strSortColumn) > 0     BEGIN             SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(".",@strSortColumn))    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(".",@strSortColumn) + 1, LEN(@strSortColumn))     END ELSE     BEGIN             SET @SortTable = @Tables             SET @SortName = @strSortColumn  END 
--设置排序字段类型和精度 SELECT @type=t.name, @prec=c.prec FROM sysobjects o        JOIN syscolumns c on o.id=c.id        JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName        IF CHARINDEX("char", @type) > 0       SET @type = @type + "(" + CAST(@prec AS varchar) + ")"   DECLARE @strPageSize nvarchar(50) DECLARE @strStartRow nvarchar(50) DECLARE @strFilter nvarchar(1000) DECLARE @strSimpleFilter nvarchar(1000) DECLARE @strGroup nvarchar(1000)   IF @pageindex <1        SET @pageindex = 1  SET @strPageSize = CAST(@PageSize AS nvarchar(50)) --设置开始分页记录数 SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  --筛选以及分组语句IF @Filter IS NOT NULL AND @Filter != ""     BEGIN             SET @strFilter = " WHERE " + @Filter + " "     SET @strSimpleFilter = " AND " + @Filter + " " END ELSE     BEGIN             SET @strSimpleFilter = ""             SET @strFilter = ""     END IF @Group IS NOT NULL AND @Group != ""     SET @strGroup = " GROUP BY " --计算总记录数DECLARE @TotalCountSql nvarchar(1000)SET @TotalCountSql=N"SELECT @TotalCount=COUNT(*)" +N" FROM " + @Tables + @strFilterEXEC sp_executesql @TotalCountSql,N"@TotalCount int OUTPUT",@TotalCount OUTPUT--执行查询语句    EXEC("DECLARE @SortColumn " + @type + "SET ROWCOUNT " + @strStartRow + "SELECT @SortColumn=" + @strSortColumn + " FROM " + @Tables + @strFilter + " " + @strGroup + " ORDER BY " + @Sort + "SET ROWCOUNT " + @strPageSize + "SELECT " + @Fields + " FROM " + @Tables + " WHERE " + @strSortColumn + @operator + " @SortColumn " + @strSimpleFilter + " " + @strGroup + " ORDER BY " + @Sort + "")