简单的分页

主要是利用排序函数Row_number()的使用

 

  declare @Table nvarchar(200)              ----作用表
  declare @PageNum int                      ----显示第几页
  declare @PageSize int                     ----每页显示条数
  declare @Orderby nvarchar(200) = ''       ----排序字段
  declare @SqlString nvarchar(4000) 
  
  SET @Table = 'DimMTM'
  SET @PageNum = 10
  SET @PageSize = 9
  SET @Orderby = 'productkey'
  
  
  IF @Orderby = ''
  
  SET @Orderby = (
      SELECT col.name FROM sys.objects obj
      join sys.columns col on obj.object_id = col.object_id
      WHERE obj.name = @Table
      and column_id = 1 
      )
  
  SET @SqlString = 
     'WITH CTE AS
      (
        SELECT ROW_NUMBER()OVER(ORDER BY '+@Orderby+') RID,* FROM '+@Table+'
        )
      
      SELECT * FROM CTE
      WHERE RID BETWEEN '+CAST((@PageNum*@PageSize+1) AS NVARCHAR) +' AND '+CAST( @PageSize*(@PageNum+1) AS NVARCHAR)
      
     EXEC (@SqlString)