排序字段值重复时的分页存储过程

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO     ALTER   PROCEDURE dbo.spSourcePage  @WhereKey VARCHAR(2000),  @SortKey VARCHAR(100),  @SortFun INT,  @PageIndex INT,  @PageSize INT  [email protected] INT OUTPUT AS BEGIN   DECLARE @StartRowIndex INT; DECLARE @Sort VARCHAR(10); DECLARE @tsql NVARCHAR(4000); SET @tsql = '';   IF LEN(@WhereKey) = 0 BEGIN     SET @WhereKey = '1=1'; END -- 根据页码和每页大小计算起始行 IF @PageSize < 1     SET @StartRowIndex = 1 SET @StartRowIndex = ( (@PageIndex-1)* @PageSize + 1);     DECLARE @PK VARCHAR(50); DECLARE @tblPK TABLE ( PK nvarchar(50) NOT NULL PRIMARY KEY );   IF @SortFun = 0 BEGIN    SET @Sort = ' DESC'; END ELSE BEGIN    SET @Sort = ' ASC'; END EXEC ('  -- 声明一个读取主键的游标  DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR  -- 这里只读取主键，并插入表里  SELECT source_id FROM Source WHERE ' + @WhereKey + ' ORDER BY ' + @SortKey + @Sort )   -- 打开游标 OPEN PagingCursor -- 直接跳到起始行 FETCH RELATIVE @StartRowIndex FROM PagingCursor INTO @PK   -- 不返回统计的行数 SET NOCOUNT ON -- 开始循环读取记录 WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 BEGIN    --INSERT @tblPK (PK)  VALUES ( @PK )    SET @tsql = ',' + @PK + @tsql    FETCH NEXT FROM PagingCursor INTO @PK    SET @PageSize = @PageSize - 1 END   CLOSE       PagingCursor DEALLOCATE  PagingCursor       if LEN(@tsql) > 0       BEGIN           SET @tsql = RIGHT(@tsql,LEN(@tsql)-1);           EXEC('SELECT * FROM Source WHERE source_id in (' + @tsql+ ') ORDER BY '  + @SortKey + @Sort)       END END     GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO