存储过程分页-CSDN博客

本文链接：https://blog.csdn.net/zhypmq/article/details/1839001

        Dim filetype As String = Request.Params("type")
        Dim whereStr As String
        '文件分类
        whereStr = " TH_key=0 "
        Dim orderstr As String
        If filetype = "1" Then
            orderstr = " order by TH_date desc"
        ElseIf filetype = "0" Then
            orderstr = " order by Th_hits desc"
        Else
            If paterId > 0 Then
                whereStr = whereStr & " and TH_paterId='" & paterId & "' "
            End If
            If ClassId <> 0 Then
                whereStr = whereStr & " and TH_classID = '" & ClassId & "' "
            End If
            orderstr = " order by TH_Id desc"
        End If
        Dim myConnection As SqlConnection = New SqlConnection(connstr)
        Dim myCommand As SqlCommand = New SqlCommand("sp_page1", myConnection)
        'Dim tablename, textname, searchs, orders As String
        Dim selects As String = " [TH_ID], [TH_smolTitle], [TH_paterId], [TH_classID],[TH_key],[th_author],[TH_zhuangtai],[TH_date]"
        With myCommand
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add(New SqlParameter("@tb", SqlDbType.NVarChar)).Value = DBarticle '数据表名
            .Parameters.Add(New SqlParameter("@col", SqlDbType.NVarChar)).Value = "TH_ID" '按该列来进行分页
            .Parameters.Add(New SqlParameter("@coltype", SqlDbType.Int)).Value = 0 ' --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
            .Parameters.Add(New SqlParameter("@orderstr", SqlDbType.NVarChar)).Value = orderstr '--排序,0-顺序,1-倒序

            .Parameters.Add(New SqlParameter("@collist", SqlDbType.NVarChar)).Value = selects '--要查询出的字段列表,*表示全部字段
            .Parameters.Add(New SqlParameter("@pagesize", SqlDbType.Int)).Value = 17 '   --每页记录数
            .Parameters.Add(New SqlParameter("@page", SqlDbType.Int)).Value = _currentPageNumber '----指定页
            .Parameters.Add(New SqlParameter("@condition", SqlDbType.NVarChar)).Value = whereStr '--查询条件
            .Parameters.Add(New SqlParameter("@pages", SqlDbType.Int)).Direction = ParameterDirection.Output '总页数
            .Parameters.Add(New SqlParameter("@counts", SqlDbType.Int)).Direction = ParameterDirection.Output '总记录数
        End With
        Try
            myConnection.Open()
            Alist1.DataSource = myCommand.ExecuteReader()
            Alist1.DataKeyNames = New String() {"TH_ID"}
            Alist1.DataBind()

          Dim _totalRecords As Int32 = CType(myCommand.Parameters("@counts").Value, Int32)
          '总页数
            _totalPages = CType(myCommand.Parameters("@pages").Value, Int32)

CREATE   PROCEDURE   sp_page1
      @tb                   varchar(50),   --表名
      @col                 varchar(50),   --按该列来进行分页
      @coltype         int,                   --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
      @orderstr     varchar(800),--排序字符串
      @collist         varchar(800),--要查询出的字段列表,*表示全部字段
      @pagesize       int,                   --每页记录数
      @page               int,                   --指定页
      @condition     varchar(800), --查询条件
      @pages             int   OUTPUT,       --总页数
      @counts   int OUTPUT    --总纪录数

AS
DECLARE @orders varchar(800), @sql nvarchar(4000),@where1 varchar(800)

IF   @condition   is   null   or   rtrim(@condition)=''
   BEGIN--没有查询条件
       SET   @where1=' '

END
ELSE
   BEGIN--有查询条件
       SET   @where1=' WHERE ('+@condition+') '--本来有条件再加上此条件
   END
IF @orderstr is null or rtrim(@orderstr)=''
Begin
  set @orders='ORDER   BY   '+@col+''
End
Else
Begin
  set @orders=@orderstr
End

SET   @sql='SELECT   @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize   AS   varchar)+ ')   FROM   '+@tb+@where1
EXEC   sp_executesql   @sql,N'@pages   int   OUTPUT',@pages   OUTPUT--计算总页数

SET @sql='SELECT @counts=CEILING((COUNT(*)+0.0)) FROM '+@tb+@where1
EXEC sp_executesql @sql,N'@counts int OUTPUT',@counts OUTPUT--计算总数

Set @sql='select ccc.* from
(
   select top ' + cast(@page*@pagesize as varchar) + ' '+@collist+ ' from '+ @tb + ' ' + @where1 + ' ' + @orders + '
) as ccc
   where ccc.' + @col + ' not in
    (
select top ' + cast((@page-1)*@pagesize as varchar) + ' '+@col+ ' from '+ @tb + ' ' + @where1 + ' ' + @orders + '
)'
IF   @page=1 or @page > @pages --第一页或者大于总页数
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+'   FROM   '+@tb+
          @where1+ ' ' + @orders
EXEC(@sql)
GO