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)--Storage dynamic SQL statements
DECLARE @tmpWhere varchar(800)
DECLARE @tmpAndWhere varchar(800)--used for query conditions on page N (>1)
DECLARE @tmpOrder varchar(200)
DECLARE @tmpD_X varchar(2)
DECLARE @tmpMin_MAX varchar(3)
--Set conditions--
IF @strWhere IS NULL OR RTRIM(@strWhere)=''
BEGIN--No query conditions
SET @tmpWhere=''
SET @tmpAndWhere=''
END
ELSE
BEGIN--There are query conditions
SET @tmpWhere=' WHERE '+@strWhere
SET @tmpAndWhere=' AND '+@strWhere
END
--Set sorting--
IF @strOrderType != 0
BEGIN--reverse order
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 query--
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
Calling method:
IntPageSize=20
strTable= [TableName] 'Data table name
strFields= Field1,Field2,Field3,Field4 'The column names to be read
strKeyField=Field1'primary key: Here it is assumed that Field1 is the primary key
strWhere='Condition: FieldA='b'
strOrderType=1'Sort method: 1 is reverse order, 0 is sequential order
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(no record)
ELSE
GetRecord=RS.GetRows(IntPageSize)
For i=0 To Ubound(GetRecord,2)
Response.Write(GetRecord(0,i),GetRecord(1,i),GetRecord(2,i))'...output content
NEXT
GetRecord=Null
END IF
SET RS=NOTHING
Helpful friends, please debug it yourself. The total records are retrieved using ASP and stored in SESSION. If you count the total records every time, it will be very time-consuming. Of course, if you want to retrieve the total records in the stored procedure, It is also possible to record and return the total number of pages. The following is the code:
--Get the total number of records--
SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhere
EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT
--Get the total number of pages--
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
Don’t forget to return the definition parameters:
@getRecordCounts int output,--Return total records
@getPageCounts int output--returns the total number of pages