基礎於SET ROWCOUNT 的分頁預存程序

Set RowCount 功能:
使 Microsoft SQL Server 在返回指定的行數之後停止處理查詢。
如
Declare @SortID --A表的整形主鍵1,2,3.....1000
Set RowCount 10
Select @SortID=SortID From [A] Order By SortID DESC
Print @SortID
結果為991
Set RowCount 10
Select * From [A] Order By SortID DESC
結果為一個記錄集,SortID 為 1000-991
我們按pagesize為10,將A表按SortID 遞減排列,取第2頁的代碼如下
Declare @SortID
Set RowCount 10 --- pageSize * (2-1)
Select @SortID=SortID From [A] Order By SortID DESC
Set Rowcount PageSize
Select * From [A] Where SortID < @SortID Order By SortID DESC

    下面的是動易的用來按不同欄位排序並分頁的一個通用預存程序(SQL2000)

ALTER  PROCEDURE [dbo].[PR_Common_GetListBySortColumn]
(
@StartRows int,
@PageSize int,
@PrimaryColumn varchar(1000),--主鍵
@SortColumnDbType  varchar(100), --排序欄位的數實值型別 如 Int ,DateTime等
@SortColumn varchar(1000),---排序的列
@StrColumn varchar(1000),--- 需要選擇的列
@Sorts varchar(100),--- ASC|DESC|空字串(ASC)
@Filter varchar(1000),--- where 後面的條件
@TableName varchar(1000),-- 表名
@Total int OUTPUT
)
AS

SET NOCOUNT ON

Begin
IF @StartRows<=0
 SET @StartRows = 0

DECLARE @equalOperator char(2)
IF @StartRows=0
 BEGIN
   SET @equalOperator = '='
   SET @StartRows = 1
 END
ELSE
  SET @equalOperator = ''

/*Set sorting variables.*/
DECLARE @operator char(2)
 
IF CHARINDEX('DESC',@Sorts)>0
 BEGIN
  SET @operator = '<' + @equalOperator
 END
ELSE
 BEGIN
  SET @operator = '>' + @equalOperator
 END
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)

IF @Filter IS NOT NULL AND @Filter!=''
 BEGIN
  SET @strFilter = ' WHERE ' + @Filter + ' '
  SET @strSimpleFilter = ' AND ' + @Filter + ' '
 END
ELSE
 BEGIN 
  SET @strFilter = ''
  SET @strSimpleFilter = ''
 END

DECLARE @Sql nVarchar(4000)
SET @Sql=N'SELECT @Total=Count(*) FROM ' + @TableName + @strFilter
Exec sp_executesql @Sql, N'@Total Int Out',@Total Out

IF @PageSize<=0
SET @PageSize=@Total

IF @PrimaryColumn!=@SortColumn
BEGIN
EXEC(
'DECLARE @SortId int ' +
'DECLARE @SortCol '+ @SortColumnDbType + ' '+
'SET ROWCOUNT '+ @StartRows + '
 SELECT @SortId = '+ @PrimaryColumn + ',@SortCol='+ @SortColumn +' FROM '+ @TableName + @strFilter +' ORDER BY '+ @SortColumn +' ' +@Sorts + ',' +  @PrimaryColumn + ' ' +@Sorts +'
 SET ROWCOUNT '+ @PageSize  + '
 SELECT '+ @StrColumn +' FROM '+ @TableName + '
 WHERE ('+ @SortColumn  + @operator+' @SortCol OR ('+ @SortColumn  + '= @SortCol AND '+ @PrimaryColumn  + @operator +' @SortId ))' + @strSimpleFilter + ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + ''
)
END
ELSE
BEGIN
EXEC(
'DECLARE @SortId int ' +
'SET ROWCOUNT '+ @StartRows + '
 SELECT @SortId = '+ @SortColumn + ' FROM '+ @TableName + @strFilter +' ORDER BY '+ @SortColumn + ' ' +@Sorts +'
 SET ROWCOUNT '+ @PageSize  + '
 SELECT '+ @StrColumn +' FROM '+ @TableName + '
 WHERE '+ @SortColumn  + @operator +' @SortId '+ @strSimpleFilter + ' ORDER BY '+ @SortColumn +' '+ @Sorts + ''
)
END

Return @Total
END

SET ROWCOUNT 0
SET NOCOUNT OFF

  說明 後面那2節動態SQL 根據SortColumn 是不是等於 PrimaryColumn (主鍵)進行分別處理,
  1.當2個相等時,只要在條件處(where )@SortColumn  + @operator +' @SortId ' 即可
  2.當主鍵不等於SortColumn時,就有可能存在 SortColumn列存在多個同值的情況,故可以看到排列語句(Order By)使用的是  ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + '',即主鍵做為第2排序對象,比方有張表B(SortID,RefTime,Name) 其中SortID為int類型主鍵,RefTime為重新整理時間,那麼我們需要按重新整理時間到序來分頁取資料, 當RefTime沒有重複時,where 條件後為@SortColumn  + @operator+ @SortCol   即可,但是當RefTime有重複時,我們需要加上 OR ('+ @SortColumn  + '= @SortCol AND '+ @PrimaryColumn  + @operator +' @SortId ) ,因為按,RefTime DESC,SortID DESC 排列後,如果當前分頁存在RefTime相同的記錄,那麼他們必定符合
@RefTime(分頁第一條)>=RefTime And @SortID > SortID 將 >=分開寫就是上面預存程序裡的代碼形式

使用這個分頁預存程序,需要目標表(記錄集)有一個值唯一的單一列(不允許組合列),這個列通常是主鍵.