sql server 存儲過程分頁

 CREATE PROCEDURE [dbo].[Select_page_data_allow_fIElds_cond]
@allow_fIElds varchar(100) =" * ",
@DefRecordNum int =20,
@PageNo int =1,
@Where varchar(500) = null
AS
declare @R_end int
declare @S_sql varchar(500)
declare @condition varchar(500)
declare @condition2 varchar(500)


--if rtrim(@Where)!=""
--if rtrim(@Where)!=""

SET @r_end=(@PageNo-1)*@DefRecordNum+1

--正則頁碼
IF ( CAST(@r_end AS INT)<1)
SET @r_end = 1

SET @Where=rtrim(@Where)
--if not (@Where is null )
if (@Where <>'' )
SET @S_sql="SELECT TOP "+CAST(@DefRecordNum AS CHAR(4)) +space(2)+@allow_fIElds+" FROM BBSReply WHERE id>=(SELECT MAX(ID) AS r_begin FROM (SELECT TOP "+CAST(@R_end AS CHAR(5))+" ID FROM BBSReply WHERE "+@Where+" ORDER BY ID) airzen) AND "+@Where+" order by id"
ELSE
SET @S_sql="SELECT TOP "+CAST(@DefRecordNum AS CHAR(4)) +space(2)+@allow_fIElds+" FROM BBSReply WHERE id>=(SELECT MAX(ID) AS r_begin FROM (SELECT TOP "+CAST(@R_end AS CHAR(5))+" ID FROM BBSReply ORDER BY ID) airzen) order by id"

--PRINT @S_sql
EXEC(@S_sql)
GO




---------------------------------------------------

<%
'********************************************************************
'本程序由AIRZEN 2004/5/12 更新修定。
'===================================================================
Option Explicit
'Response.Flush
Dim BeginTime,EndTime
BeginTime=Timer
Dim conn,SQLstr,Rs,DefRecordNum,CursorBegin,CursorEnd,CurPageNum,hav
DefRecordNum=20
dim R_end
'--------------獲取相關參數----------
If Request("CurPageNum")<>"" Then
CurPageNum=CLng(Request("CurPageNum"))
If CurPageNum<=0 Then CurPageNum=1
Else
CurPageNum=1
E nd If



'----------------End-----------------
'------------顯示翻頁內容函數--------
Function TurnPageFS(DispRecordNum)
Dim n
While Not(Rs.Eof) And nn=n+1
Response.Write ""&_
""&n&""&_
""&Rs(0)&""&_
""&Rs(1)&""&_
""&Rs(2)&""&_
""&Rs(3)&""&_
""&Rs(4)&""&_
""&Rs(5)&""&_
""
If n=1 Then CursorBegin=Rs(0)
If n=DefRecordNum Or Rs.Eof Then CursorEnd=Rs(0)
Rs.MoveNext
Wend
End Function
'-------------連接數據庫-------------
Set conn=Server.CreateObject("Adodb.Connection")
'SQLstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.Mappath("mldata.mdb")
SQLstr="provider=SQLOLEDB;server=(local);database=XX;uid=XX;pwd=XX;"
conn.Open SQLstr
Dim TotalRecords,TotalPages

TotalPages=clng(request.QueryString("TotalPages"))

'//判斷有無接收的頁碼
if TotalPages=0 or not isnumeric(TotalPages) then '//無接收頁碼