優(yōu)秀的數(shù)據(jù)分頁存儲過程代碼(ver2.0)

數(shù)據(jù)庫連接出錯，請檢查數(shù)據(jù)庫連接字串
    "
    Response.End
    End If
    '本類進行實例化代碼，不將所有可能性條件分頁考慮進去。本類僅作參考
    '--------------------------------------------------------------------------------------------
    'PageNo-當前頁;PageSizeX-分頁大小;PageCountX-總頁數(shù);FieldCount-總記錄數(shù);StrWhere-查詢條件
    Dim PageNo,PageSizeX,PageCountX,FieldCount,StrWhere
    '進行賦值
    PageNo=trim(Request.Querystring("PageNo"))
    PageSizeX=30 '定義分頁大小
    if isnumeric(PageNo) or PageNo="" then '如果沒有Page值，進行初始化值
    PageNo=abs(PageNo)
    if PageNo=0 then PageNo=1
    else
    PageNo=1
    end if
    StrWhere="(ClassCode=0101 and Hits>20)" '注意，此處注意字符的長度
    set recom = server.createobject("adodb.command")
    recom.activeconnection = Conn
    recom.commandtype = 4
    recom.commandtext = "News_Class"
    recom.Parameters.Append recom.CreateParameter("@StrWhere",202,1,100,StrWhere)
    recom.Parameters.Append recom.CreateParameter("@PageSize",3,1,,PageSizeX)
    recom.Parameters.Append recom.CreateParameter("@PageIndex",3,1,,PageNo)
    set rs = recom.execute ()
    if rs.eof then
    set rs=nothing : set recom=nothing : Conn.Close : set Conn=nothing
    response.write ""
    response.end
    end if
    '顯示數(shù)據(jù)
    do while not rs.eof
    response.write ""&rs("Title")&"   添加時間："&rs("AddTime")&"
    "
    rs.movenext
    loop
    '取得記錄總數(shù)，計算頁數(shù)
    set rs = rs.NextRecordset
    if rs("countx") > 0 then
    FieldCount=rs( "countx")
    else
    FieldCount=0
    end if
    if (FieldCount mod PageSizeX)>0 then
    PageCountX=((FieldCount - (FieldCount mod PageSizeX))/ PageSizeX)+1
    else
    PageCountX= (FieldCount / PageSizeX)
    end if
    '進行關(guān)閉和釋放相關(guān)資源
    set rs=nothing : set recom=nothing : Conn.Close : set Conn=nothing
    '顯示分頁
    if PageNo<=1 then
    Response.Write "[首頁] [上一頁]"
    else
    Response.Write "[首頁] "
    Response.Write "[上一頁] "
    end if
    if PageNo>=PageCountX then
    Response.Write "[下一頁] [尾頁]"
    else
    Response.Write "[下一頁] "
    Response.Write "[尾頁]"
    end if
    Response.Write"[頁次 第"&PageNo&"頁/共" & PageCountX &"頁]"
    Response.Write" [共"&FieldCount&"條 "& PageSizeX & "條/頁]"
    Response.Write"轉(zhuǎn)到" & "" & "頁"
    Response.Write""
    Response.Write""
    %>
    '--------------------------------------------------------以下代碼為存儲過程代碼
    CREATE procedure News_Class
    --資訊新聞分頁
    (
    @StrWhere varchar(100),
    @PageSize int,
    @PageIndex int
    )
    AS
    declare @strSQL varchar(2000) -- 主語句
    declare @strCountSQL varchar(2000) -- 總記錄主語句
    declare @strTmp varchar(1000) -- 臨時變量
    Set @strTmp =" Select top " + str(@PageSize) + " Title,AddTime from Tb_News " --此處注意，需幾個字段讀幾個字段
    if @StrWhere<>''
    Begin
     Set @strSQL=@strTmp + " where ID < (select min(ID) from (select top " + str((@PageIndex-1)*@PageSize)+" ID from Tb_News Where "+@StrWhere+" order by ID desc) as tblTmp ) and "+@StrWhere+" order by ID desc"
     set @strCountSQL="select count(ID) as countx from Tb_News Where "+@StrWhere+" "
     End
    else
    Begin
     Set @strSQL=@strTmp + " where ID < (select min(ID) from (select top " + str((@PageIndex-1)*@PageSize)+" ID from Tb_News order by ID desc) as tblTmp ) order by ID desc"
     set @strCountSQL="select count(ID) as countx from Tb_News "
     End
    if @PageIndex = 1
    if @StrWhere <>''
    Begin
     Set @strSQL=@strTmp +" Where "+@StrWhere+" order by ID desc"
    End
    else
    Begin
     Set @strSQL=@strTmp +" order by ID desc"
    End
    exec (@strSQL)
    exec (@strCountSQL)
    GO