CREATE PROCEDURE GoalerPageSp
@IntPageSize int,
@IntCurrPage int,
@strFields nvarchar(2000),
@strTable varchar(200),
@strWhere varchar(800),
@strOrderType varchar(200),
@strKeyField varchar(50)
AS
SET NOCOUNT ON
DECLARE @tmpSQL nvarchar(4000)--存放動態SQL語句
DECLARE @tmpWhere varchar(800)
DECLARE @tmpAndWhere varchar(800)--用於第N(>1)頁上邊的查詢條件
DECLARE @tmpOrder varchar(200)
DECLARE @tmpD_X varchar(2)
DECLARE @tmpMin_MAX varchar(3)
--設定條件--
IF @strWhere IS NULL OR RTRIM(@strWhere)=''
BEGIN--沒有查詢條件
SET @tmpWhere=''
SET @tmpAndWhere=''
END
ELSE
BEGIN--有查詢條件
SET @tmpWhere=' WHERE '+@strWhere
SET @tmpAndWhere=' AND '+@strWhere
END
--設定排序--
IF @strOrderType != 0
BEGIN--倒序
SET @tmpD_X = '<'
SET @tmpMin_MAX = 'MIN'
SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' DESC'
END
ELSE
BEGIN
SET @tmpD_X = '>'
SET @tmpMin_MAX = 'MAX'
SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' ASC'
END
--SQL查詢--
IF @IntCurrPage=1
Set @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder
ELSE
SET @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' WHERE ('+@strKeyField+' '+@tmpD_X+' (SELECT '+@tmpMin_MAX+'(' +@strKeyField+') FROM (SELECT TOP '+CAST(@IntPageSize*(@IntCurrPage-1) AS VARCHAR)+' '+@strKeyField+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder+') AS T))'+@tmpAndWhere+' ' +@tmpOrder
EXEC(@tmpSQL)
GO
調用方法：
IntPageSize=20
strTable= [TableName] '資料表名稱
strFields= Field1,Field2,Field3,Field4 '需要讀取的列名
strKeyField=Field1'主鍵：這裡假設Field1為主鍵
strWhere='條件：FieldA='b'
strOrderType=1'排序方式：1為倒序，0為順序
CurrPage=Request.QueryString(Page)
IF(CurrPage<> And Isnumeric(CurrPage))THEN
CurrPage=CLNG(CurrPage)
IF(CurrPage<1)THEN CurrPage=1
ELSE
CurrPage=1
END IF
IF strWhere<> THEN
tmpWhere= WHERE &strWhere
ELSE
tmpWhere=
END IF
IF(SESSION(RecCount)<>)THEN
IF(SESSION(strWhere)<>strWhere)THEN
RecCount=Conn.Execute(SELECT COUNT(&strKeyField&) FROM &strTable&tmpWhere)(0)
SESSION(RecCount)=RecCount
SESSION(strWhere)=strWhere
ELSE
RecCount=SESSION(RecCount)
END IF
ELSE
RecCount=Conn.Execute(SELECT COUNT(*) FROM &strTable&tmpWhere)(0)
SESSION(RecCount)=RecCount
SESSION(strWhere)=strWhere
END IF
IF(RecCount MOD IntPageSize <>0)THEN
IntPageCount=INT(RecCount/IntPageSize)+1
ELSE
IntPageCount=RecCount/IntPageSize
END IF
SET Cmd=Server.CreateObject(Adodb.Command)
Cmd.CommandType=4
SET Cmd.ActiveConnection=Conn
Cmd.CommandText=GoalerPageSp
Cmd.Parameters.Append Cmd.CreateParameter(@IntPageSize,4,1,4,IntPageSize)
Cmd.Parameters.Append Cmd.CreateParameter(@IntCurrPage,4,1,4,CurrPage)
Cmd.Parameters.Append Cmd.CreateParameter(@strFields,200,1,2000,strFields)
Cmd.Parameters.Append Cmd.CreateParameter(@strTable,200,1,200,strTable)
Cmd.Parameters.Append Cmd.CreateParameter(@strWhere,200,1,800,strWhere)
Cmd.Parameters.Append Cmd.CreateParameter(@strOrderType,4,1,4,strOrderType)
Cmd.Parameters.Append Cmd.CreateParameter(@strKeyField,200,1,50,strKeyField)
SET RS=Cmd.Execute()
IF RecCount<1 THEN
Response.Write(沒有記錄)
ELSE
GetRecord=RS.GetRows(IntPageSize)
For i=0 To Ubound(GetRecord,2)
Response.Write(GetRecord(0,i),GetRecord(1,i),GetRecord(2,i))'...輸出內容
NEXT
GetRecord=Null
END IF
SET RS=NOTHING
有用的朋友請自己慢慢調試吧，總記錄是用ASP來取的，存放在SESSION裡邊，如果每次都統計一次總記錄，將會非常費時，當然，如果你想在存儲過程裡來取總記錄和總頁數然後返回也是可以的，下邊是代碼：
--取得記錄總數--
SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhere
EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT
--取得總頁數--
SET @tempFolatNumber=@getRecordCounts%@IntPageSize
IF @getRecordCounts<=@IntPageSize
SET @getPageCounts=1
ELSE
BEGIN
IF @tempFolatNumber != 0
SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1
ELSE
SET @getPageCounts=(@getRecordCounts/@IntPageSize)
END
別忘了回傳定義參數：
@getRecordCounts int output,--傳回總記錄
@getPageCounts int output--傳回總頁數