MS SQL Paging 語法

轉錄自PTT
CREATE  PROCEDURE sp_Paging
@tblName varchar(255) ,
@strGetFields varchar(1000) = '*',
@fldName varchar(255)='',
@pageSize int = 10,
@pageIndex int = 1,
@recordCount int = 0 output,
@pageCount int = 0 output,
@orderType int = 0,
@strWhere varchar(1500) = ''
AS
declare @strSQL nvarchar(3000)
declare @strTmp varchar(110)
declare @strOrder varchar(400)

    begin
        if @strWhere !=''
            set @strSQL = 'select @recordCount = count(*) from ' + @tblName + ' where '+@strWhere

 else
            set @strSQL = 'select @recordCount = count(*) from ' + @tblName
    end

    exec sp_executesql @strSQL,N'@recordCount int out ',@recordCount out

    if @recordCount % @pageSize = 0
        set @pageCount = @recordCount / @pageSize
    else
        set @pageCount = @recordCount / @pageSize + 1
  --計算出記錄是總數和求出總共的頁數

   begin
      if @orderType != 0
         begin
            set @strTmp = '<(select min'
            set @strOrder = ' order by ' + @fldName +' desc'
         --如果@orderType不是0﹐就執行降序﹐這句很重要﹗
         end
      else

       begin
            set @strTmp = '>(select max'
            set @strOrder = ' order by ' + @fldName +' asc'
         end

     if @pageIndex = 1
        begin
           if @strWhere != ''

              set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
           else

              set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
        end
     else
         begin
           if @strWhere != ''
                 set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
                        + @tblName + ' where ' + @fldName + '' + @strTmp + '('
                        + substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
                        + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
                        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
       else
         set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
                        + @tblName + ' where ' + @fldName + '' + @strTmp + '('
                        + substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ')　from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
                        + @fldName + ' 　from ' + @tblName + '' + @strOrder + ') as tblTmp)'
                        + @strOrder
         end
   end
--print @strSQL

exec sp_executesql @strSQL
GO