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

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

 --@RowCount 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