分页

USE [DTMIS_LZ]
GO
/****** Object:  StoredProcedure [dbo].[spPaginationPK]    Script Date: 03/10/2016 18:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER  PROCEDURE [dbo].[spPaginationPK]
    @TableName varchar(2000),        --表名或视图名
    @Fields varchar(5000)='*',        --要返回的列
    @OrderField varchar(5000),        --排序字段
    @PK varchar(255),                --主键
    @SqlWhere varchar(max) = '',    --查询条件（不要加WHERE）
    @PageSize int,                    --页尺寸
    @PageIndex int=1,                --页码
    @TotalPage int OUTPUT,            --总页数
    @TotalRecord int OUTPUT            --记录总数 
AS
DECLARE @strSql nvarchar(max)
DECLARE @strOrder nvarchar(max)

IF @SqlWhere IS NULL
    BEGIN
        SET @SqlWhere = ''
    END 
IF len(@SqlWhere) > 0
    BEGIN
        SET @strSql = N'SELECT  @TotalRecord =  COUNT(*)  FROM ' + @TableName + N' WHERE ' + @SqlWhere
    END
ELSE
    BEGIN
        SET @strSql =N'SELECT  @TotalRecord =  COUNT(*)  FROM ' + @TableName
    END
    
EXEC sp_executesql @strSql,N'@TotalRecord int OUTPUT',@TotalRecord OUTPUT
SET @TotalPage = ceiling(@TotalRecord * 1.0 / @PageSize)

DECLARE @sql varchar(max)

--实际总共的页码小于当前页码   或者   最大页码 
IF  @TotalPage >= 1
    --如果分页后页数大于0 
   BEGIN 
       IF @TotalPage <= @PageIndex and @TotalPage >=1
          --如果实际总共的页数小于datagrid索引的页数 
          --or   @TotalPage=1 
         BEGIN 
         --设置为最后一页 
             SET @PageIndex=@TotalPage 
         END 
      IF @TotalPage <= @PageIndex and @TotalPage=0 
         BEGIN 
             SET @PageIndex=1;
         END 
   END 
   
   DECLARE @ReSerial int
   SET @ReSerial=(@PageIndex-1)*@PageSize
   
IF @PageIndex = 1 or @TotalPage <= 1     --如果为第一页 
   BEGIN 
      IF len(@SqlWhere)   =0 
         BEGIN 
            SET   @sql = N'SELECT TOP ' + str(@PageSize) + N' ' + @Fields + N',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+N' as rowId FROM ' + @TableName + N' ORDER BY ' + @OrderField + N' '
         END 
      ELSE 
         BEGIN 
             SET   @sql = N'SELECT TOP ' + str( @PageSize) + N' ' + @Fields + N',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+N' as rowId FROM ' + @TableName + N' WHERE ' + @SqlWhere + N' ORDER BY ' + @OrderField + N' '
       END 
   END 
ELSE IF @PageIndex = @TotalPage   --如果为最后一页                       
   BEGIN 
       IF len(@SqlWhere) = 0 
          BEGIN 
              SET @sql = N'SELECT ' + @Fields + N',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+N' as rowId FROM ' + @TableName + N' WHERE ' + @PK + N' not in(SELECT top ' + str(@PageSize * (@PageIndex - 1))  + N' ' + @PK+ N' FROM ' + @TableName + N' ORDER BY ' + @OrderField + N' ' + N') ORDER BY '+ @OrderField + N' '
          END 
       ELSE 
          BEGIN 
             SET  @sql = ' SELECT ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+' as rowId FROM ' + @TableName + ' WHERE ' + @PK + ' not in (SELECT top ' + str(@PageSize *  (@PageIndex - 1)) + ' ' + @PK + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ' ORDER BY ' + ' ' + @OrderField + ' ' + ') AND ' + @SqlWhere + ' ORDER BY ' + @OrderField + ' '
          END 
   END 
ELSE   --否则执行   
   BEGIN 
       IF len(@SqlWhere) = 0
          BEGIN 
              SET  @sql = N'SELECT TOP ' + str(@PageSize)  + N' ' + @Fields + N',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+N' as rowId  FROM ' + @TableName + N'  WHERE '+ @PK + N' not in(SELECT top ' + str(@PageSize *  (@PageIndex - 1)) + N' ' + @PK + N' FROM ' + @TableName + N' ORDER BY ' + @OrderField + N' ' + N') ORDER BY ' + @OrderField + N' '
          END 
       ELSE 
          BEGIN 
             SET @sql = 'SELECT TOP ' + str(@PageSize)  + ' ' + @Fields+ ',ROW_NUMBER() Over(order by ' + @OrderField + ')+'+str(@ReSerial)+' as rowId FROM ' + @TableName + ' WHERE ' + @PK + ' not in(SELECT top ' + str(@PageSize *  (@PageIndex - 1)) + ' ' + @PK + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ' ORDER BY ' + @OrderField + ' ' + ' ) and ' + @SqlWhere + ' ORDER BY ' + @OrderField + ' '
          END 
    END
EXEC(@sql)

USE [DTMIS_LZ]
GO
/****** Object:  StoredProcedure [dbo].[spPagination]    Script Date: 03/10/2016 18:32:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[spPagination]
 @TableName varchar(2000),        --表名
 @Fields varchar(5000) = '*',    --字段名(全部字段为*)
 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 @PK varchar(255),                --主键(不使用)
 @SqlWhere varchar(5000) = '',--条件语句(不用加where)
 @PageSize int,                    --每页多少条记录
 @PageIndex int = 1 ,            --指定当前为第几页
 @TotalPage int output,            --返回总页数 
 @TotalRecord int output
as
begin

    Declare @sql nvarchar(max);

    --计算总记录数         
    if (@SqlWhere='' or @SqlWhere is NULL)
        set @sql = 'select @TotalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @TotalRecord = count(*) from ' + @TableName + ' where ' + @SqlWhere

    EXEC sp_executesql @sql,N'@TotalRecord int OUTPUT',@TotalRecord OUTPUT--计算总记录数 

    --计算总页数
    select @TotalPage=CEILING((@TotalRecord+0.0)/@PageSize)

    if (@SqlWhere='' or @SqlWhere is NULL)
        set @sql = 'select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' into #t from ' + @TableName 
    else
        set @sql = 'select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' into #t from ' + @TableName + ' where ' + @SqlWhere    
        
    --处理页数超出范围情况
    if @PageIndex<=0 
        Set @PageIndex = 1
    
    if @PageIndex>@TotalPage
        Set @PageIndex = @TotalPage

     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1

    --继续合成sql语句
    --set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
    

     --得到临时表
     set @sql=@sql+'; select * from  #t  ' + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     set @sql=@sql+';drop table #t'
     Exec(@Sql) 
    
     Return @TotalRecord ---返回记录总数
  
end