求支持任意SQL语句的分页存储过程解决办法

Create PROC P_viewPage             /**//*              nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7  QQ:34813284              敬告：适用于单一主键或存在唯一值列的表或视图              ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围              */              @TableName VARCHAR(200),     --表名              @FieldList VARCHAR(2000),    --显示列名，如果是全部字段则为*              @PrimaryKey VARCHAR(100),    --单一主键或唯一值键              @Where VARCHAR(2000),        --查询条件 不含'where'字符，如id>10 and len(userid)>9              @Order VARCHAR(1000),        --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc              --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷              @SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法              @RecorderCount INT,          --记录总数 0:会返回总记录              @PageSize INT,               --每页输出的记录数              @PageIndex INT,              --当前页数              @TotalCount INT OUTPUT ,      --记返回总记录              @TotalPageCount INT OUTPUT   --返回总页数          AS          SET NOCOUNT ON              IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0              SET @Order = RTRIM(LTRIM(@Order))              SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))              SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')              WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0                  BEGIN                      SET @Order = REPLACE(@Order,', ',',')                      SET @Order = REPLACE(@Order,' ,',',')                  END              IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''                      or ISNULL(@PrimaryKey,'') = ''                      or @SortType < 1 or @SortType >3                      or @RecorderCount  < 0 or @PageSize < 0 or @PageIndex < 0                  BEGIN                      PRINT('ERR_00')                      RETURN                  END              IF @SortType = 3                  BEGIN                      IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')                          BEGIN PRINT('ERR_02') RETURN END                  END              DECLARE @new_where1 VARCHAR(1000)              DECLARE @new_where2 VARCHAR(1000)              DECLARE @new_order1 VARCHAR(1000)              DECLARE @new_order2 VARCHAR(1000)              DECLARE @new_order3 VARCHAR(1000)              DECLARE @Sql VARCHAR(8000)              DECLARE @SqlCount NVARCHAR(4000)              IF ISNULL(@where,'') = ''                  BEGIN                      SET @new_where1 = ' '                      SET @new_where2 = ' Where  '                  END              ELSE                  BEGIN                      SET @new_where1 = ' Where ' + @where                      SET @new_where2 = ' Where ' + @where + ' AND '                  END              IF ISNULL(@order,'') = '' or @SortType = 1  or @SortType = 2                  BEGIN                      IF @SortType = 1                          BEGIN                              SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'                              SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'                          END                      IF @SortType = 2                          BEGIN                              SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'                              SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'                          END                  END              ELSE                  BEGIN                      SET @new_order1 = ' orDER BY ' + @Order                  END                  IF @SortType = 3 AND  CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0              BEGIN                  SET @new_order1 = ' orDER BY ' + @Order                  SET @new_order2 = @Order + ','                  SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')                  SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')                  SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)                  IF @FieldList <> '*'                      BEGIN                          SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')                          SET @FieldList = ',' + @FieldList                          WHILE CHARINDEX(',',@new_order3)>0                              BEGIN                                  IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0                                      BEGIN                                          SET @FieldList =                                          @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))                                      END                                  SET @new_order3 =                                  SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))                              END                          SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))                      END                  END                  SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'              + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'              IF @RecorderCount  = 0                  BEGIN                      EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',                      @TotalCount OUTPUT,@TotalPageCount OUTPUT                  END              ELSE                  BEGIN                      Select @TotalCount = @RecorderCount                END              IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)                  BEGIN                      SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)                  END              IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)                  BEGIN                      IF @PageIndex = 1 --返回第一页数据                          BEGIN                              SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '                              + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1                    END                      IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据                          BEGIN                              SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('                              + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))                              + ' ' + @FieldList + ' FROM '                              + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '                              + @new_order1                          END                  END              ELSE