[MSSQL]2分处理数据分页存储过程

来源：互联网发布：js innerhtml value 编辑：程序博客网时间：2024/06/04 20:45

----2分处理数据分页存储过程
Create PROCEDURE ZBPagination
@TblName      nvarchar(1000),        ----要显示的表或多个表的连接
@FldName      nvarchar(1000) = '*', ----要显示的字段列表
@PageSize     int = 10,              ----每页显示的记录个数
@Page         int = 1,               ----要显示那一页的记录
@PageCount    int = 1 output,        ----查询结果分页后的总页数
@Counts       int = 1 output,        ----查询到的记录数
@FldSort      nvarchar(200) = 'ID', ----排序字段(列要主键或唯一值)
@Sort         bit = 0,               ----排序方法，0为升序，1为降序
@StrCondition nvarchar(1000) = null, ----查询条件,不需where
@ID           nvarchar(150) = 'ID', ----主表的主键(要跟排序列的第一列一样)
@Dist         bit = 0                ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
AS
SET NOCOUNT ON

Declare @StrTmp nvarchar(3000) ----存放取得查询结果总数的查询语句和动态生成的SQL语句

Declare @StrSortType nvarchar(10) ----数据排序规则A
Declare @StrFSortType nvarchar(10) ----数据排序规则B

Declare @SQLSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SQLCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造

IF @Dist = 0
   Begin
      Set @SQLSelect = 'Select '
      Set @SQLCounts = 'Count(1)'
   End
Else
   Begin
      Set @SQLSelect = 'Select Distinct '
      Set @SQLCounts = 'Count(DISTINCT ' + @ID + ')'
   End

If @Sort=0
   Begin
      Set @StrFSortType=' ASC '
      Set @StrSortType=' DESC '
   End
Else
   Begin
      Set @StrFSortType=' DESC '
      Set @StrSortType=' ASC '
   End

--------生成查询语句--------
--此处@StrTmp为取得查询结果数量的语句
Set @StrTmp = @SQLSelect+' @Counts='+@SQLCounts+' FROM ' + @TblName + ' Where (1=1) ' + Isnull(@StrCondition,'')

----取得查询结果总数量-----
Exec Sp_executesql @StrTmp,N'@Counts int OutPut',@Counts OutPut

Declare @TmpCounts Int
If @Counts = 0
Set @TmpCounts = 1
Else
Set @TmpCounts = @Counts

--/*-----数据分页2分处理-------*/
Declare @Lastcount Int --看最后一页还有多少条记录

    Set @PageCount = @TmpCounts/@PageSize --总数/页大小   总页数如 109 / 10 = 10
    set @Lastcount = @TmpCounts%@PageSize --总数%页大小   求模(余数) 109 % 10 = 9
    If @Lastcount > 0
       Set @PageCount = @PageCount + 1
    Else
       Set @Lastcount = @Pagesize

    /*当前页小于1 取第一页 */
    If @Page < 1
       Set @Page = 1

    /*当前页大于总页数取最后一页*/
    If @Page > @PageCount
       Set @Page = @PageCount

    --//***显示分页
    If @PageCount<2 or @Page<=@PageCount / 2 + @PageCount % 2   --前半部分数据处理
       Begin
          IF @Page=1
             Set @StrTmp=@SQLSelect+' Top '+ Cast(@PageSize as Varchar(10))+' '+ @FldName+' From '+ @TblName + ' Where (1=1) ' + Isnull(@StrCondition,'') + ' Order By '+ @FldSort +' '+ @StrFSortType
          Else
            Begin
               IF @Sort = 0   --升序
                   Begin
                      Set @StrTmp=@SQLSelect+' Top '+ Cast(@PageSize as Varchar(10))+' '+ @FldName+' From '+ @TblName
                           + ' Where ' + @ID + ' > (Select Max('+ @ID +') From ('+ @SQLSelect+' top '+ Cast(@PageSize*(@Page-1) as Varchar(10)) +' '+ @ID +' From '+ @TblName
                           + ' Where (1=1) ' + Isnull(@StrCondition,'') +' Order By '+ @FldSort +' '+ @StrFSortType+') AS TBMinID)'
                           + ' '+ Isnull(@StrCondition,'') +' Order By '+ @FldSort +' '+ @StrFSortType
                   End
                Else           --降序
                   Begin
                      Set @StrTmp=@SQLSelect+' Top '+ Cast(@PageSize as Varchar(10))+' '+ @FldName+' From ' + @TblName
                           + ' Where ' + @ID + ' <(Select Min('+ @ID +') From ('+ @SQLSelect+' Top '+ Cast(@PageSize*(@Page-1) as Varchar(10)) +' '+ @ID +' From '+ @TblName
                           + ' Where (1=1) ' + Isnull(@strCondition,'') +' Order By '+ @FldSort +' '+ @StrFSortType+') AS TBMinID)'
                           + ' '+ Isnull(@strCondition,'') +' Order By '+ @FldSort +' '+ @StrFSortType
                   End
           End
       End
    Else
       Begin
          Set @Page = @PageCount-@Page+1 --后半部分数据处理
          If @Page <= 1 --最后一页数据显示
             Set @StrTmp=@SQLSelect + ' * From (' + @SQLSelect + ' Top '+ Cast(@LastCount as Varchar(10))+' '+ @FldName+' From ' + @TblName
                  +' Where (1=1) '+ Isnull(@StrCondition,'') +' Order By '+ @FldSort + ' ' + @StrSortType+') As TempTB'+' Order by '+ @FldSort +' '+ @StrFSortType
          Else
            Begin
               IF @Sort = 0 --升序
                  Begin
                     Set @StrTmp=@SQLSelect+' * From (' + @SQLSelect + ' Top '+ Cast(@PageSize as Varchar(10))+' '+ @FldName+' From ' + @TblName
                         +' Where ' + @ID + ' <(Select Min('+ @ID +') From('+ @SQLSelect+' Top '+ Cast(@PageSize*(@Page-2)+@Lastcount as Varchar(10)) +' '+ @ID +' From ' + @TblName
                         +' Where (1=1) '+ Isnull(@strCondition,'') +' Order By ' + @FldSort + ' ' + @StrSortType+') AS TBMaxID)'
                         +' '+ Isnull(@StrCondition,'') + ' Order By '+ @FldSort + ' ' + @StrSortType+') AS TempTB'+' Order By '+ @FldSort +' '+ @StrFSortType
                  End
               Else                 --降序
                  Begin
                     Set @StrTmp=@SQLSelect+' * From (' + @SQLSelect + ' Top '+ Cast(@PageSize as Varchar(10))+' '+ @FldName+' From ' + @TblName
                         + ' Where ' + @ID + ' >(Select Max('+ @ID +') From('+ @SQLSelect+' Top '+ Cast(@PageSize*(@Page-2)+@Lastcount as Varchar(20)) +' '+ @ID +' From ' + @TblName
                         + ' Where (1=1) '+ Isnull(@StrCondition,'') +' Order By '+ @FldSort +' '+ @StrSortType+') AS TBMaxID)'
                         + ' ' + Isnull(@StrCondition,'') + ' Order By '+ @FldSort +' '+ @StrSortType+') AS TempTB'+' Order By '+ @FldSort +' '+ @StrFSortType
                  End
            End
       End

------返回查询结果-----

Exec Sp_executesql @strTmp
SET NOCOUNT OFF

GO
/*
--调用方法
--没有进行模糊查找
Declare @Count            Int
Declare @PageCount Int
Declare @ColStr            Varchar(500)
Set @ColStr = 'ID,InputDate,SupplierName,SupplierNameChs'
Exec ZBPagination @tblName = 'Supplier',@fldName = @ColStr,
                                 @PageSize = 5,@Page = 80,@pageCount = @PageCount OutPut,
                                 @Counts = @Count OutPut,@fldSort = 'ID DESC,SupplierName',
                                 @Sort = 0,@ID = 'ID'
Select @PageCount,@Count

--模糊查找
Declare @Count            Int
Declare @PageCount Int
Declare @ColStr            Varchar(500)
Set @ColStr = 'ID,InputDate,SupplierName,SupplierNameChs'
Exec ZBPagination @tblName = 'Supplier',@fldName = @ColStr,@PageSize = 5,
                                  @Page = 40,@pageCount = @PageCount OutPut,
                                  @Counts = @Count OutPut,@fldSort = 'ID DESC,SupplierName',
                                  @strCondition = ' And SupplierNameChs Like ''%有限公司%''',
                                  @Sort = 0,@ID = 'ID'
Select @PageCount,@Count
*/