'''''''''''''''''''''''''''''''''SQL數據庫 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SqlLocalName   ="(local)"        '連接IP  [ 本地用 (local) 外地用IP ]
SqlUsername    ="sa"             '數據庫用戶名
SqlPassword    ="XXXXXX"         '用戶密碼
SqlDatabaseName="NewsTable"       '數據庫名
ConnStr = "Provider=Sqloledb;User ID=" & SqlUsername & "; Password=" & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source=" & SqlLocalName & ";"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.open ConnStr

If Err Then
   err.Clear
   Set Conn = Nothing
   Response.Write "<div style='font-size:14px;color=#ff0000' align='center'>數據庫連接出錯，請檢查數據庫連接字串</div>"
   Response.End
End If

'本類進行實例化代碼，不將所有可能性條件分頁考慮進去。本類僅作參考
'--------------------------------------------------------------------------------------------

'PageNo-當前頁;PageSizeX-分頁大小;PageCountX-總頁數;FieldCount-總記錄數;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 "<script LANGUAGE='javascript'>alert('當前沒有找到任何記錄，請返回重新操作！');history.go(-1);</script>"
   response.end
end if

'顯示數據
do while not rs.eof
   response.write""&rs("Title")&"&nbsp;&nbsp;&nbsp;添加時間："&rs("AddTime")&"<br/>"
rs.movenext
loop

'取得記錄總數，計算頁數
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

'進行關閉和釋放相關資源
set rs=nothing : set recom=nothing  : Conn.Close : set Conn=nothing

'顯示分頁
if PageNo<=1 then
   Response.Write "<font color='#FF0000'>[首頁] [上一頁]</font>"
else
   Response.Write"[<a href='?PageNo=1' target='_self' title='首頁'><font color='#FF0000'>首頁</font></a>] "
   Response.Write"[<a href='?PageNo="&(PageNo-1)&"' target='_self' title='上一頁'><font color='#FF0000'>上一頁</font></a>] "
end if

if PageNo>=PageCountX then
    Response.Write "<font color='#FF0000'>[下一頁] [尾頁]</font>"
else
    Response.Write"[<a href='?PageNo="&(PageNo+1)&"'  target='_self' title='下一頁'><font color='#FF0000'>下一頁</font></a>] "
    Response.Write"[<a href='?PageNo="&PageCountX&"' target='_self' title='尾頁'><font color='#FF0000'>尾頁</font></a>]"
end if

Response.Write"[頁次 第<font color=red>"&PageNo&"</font>頁/共<font color=red>" & PageCountX &"</font>頁]"
Response.Write" [共<font color=red>"&FieldCount&"</font>條 <font color=red>"& PageSizeX & "</font>條/頁]"

Response.Write"轉到" & "<input id=zhuanpage name=zhuanpage size=2 value="&PageNo&">" & "頁<input type=submit value=""Go"" onclick=""gotoPage()"">"
Response.Write"</td></tr></table>"
Response.Write"<script>function gotoPage(){var pg=document.getElementById('zhuanpage').value;if(isNaN(pg)||pg>"&PageCountX&"||pg<0){window.alert('請正確輸入頁碼,只能為正整數!且不能大于"&PageCountX&"');return false;}else{window.location.href='?PageNo='+document.getElementById('zhuanpage').value+'';return true;}}</script>"
%>


'--------------------------------------------------------以下代碼為存儲過程代碼
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