CREATEPROCEDUREGoalerPageSp
@IntPageSizeint,
@IntCurrPageint,
@strFieldsnvarchar(2000),
@strTablevarchar(200),
@strWherevarchar(800),
@strOrderTypevarchar(200),
@strKeyFieldvarchar(50)
AS
SETNOCOUNTON
DECLARE@tmpSQLnvarchar(4000)--存放动态SQL语句
DECLARE@tmpWherevarchar(800)
DECLARE@tmpAndWherevarchar(800)--用于第N(>1)页上边的查询条件
DECLARE@tmpOrdervarchar(200)
DECLARE@tmpD_Xvarchar(2)
DECLARE@tmpMin_MAXvarchar(3)
--设置条件--
IF@strWhereISNULLORRTRIM(@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='ORDERBY'+@strKeyField+'DESC'
END
ELSE
BEGIN
SET@tmpD_X='>'
SET@tmpMin_MAX='MAX'
SET@tmpOrder='ORDERBY'+@strKeyField+'ASC'
END
--SQL查询--
IF@IntCurrPage=1
Set@tmpSQL='SELECTTOP'+CAST(@IntPageSizeASVARCHAR)+''+@strFields+'FROM'+@strTable+''+@tmpWhere+''+@tmpOrder
ELSE
SET@tmpSQL='SELECTTOP'+CAST(@IntPageSizeASVARCHAR)+''+@strFields+'FROM'+@strTable+'WHERE('+@strKeyField+''+@tmpD_X+'(SELECT'+@tmpMin_MAX+'('+@strKeyField+')FROM(SELECTTOP'+CAST(@IntPageSize*(@IntCurrPage-1)ASVARCHAR)+''+@strKeyField+'FROM'+@strTable+''+@tmpWhere+''+@tmpOrder+')AST))'+@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<>""AndIsnumeric(CurrPage))THEN
CurrPage=CLNG(CurrPage)
IF(CurrPage<1)THENCurrPage=1
ELSE
CurrPage=1
ENDIF
IFstrWhere<>""THEN
tmpWhere="WHERE"&strWhere
ELSE
tmpWhere=""
ENDIF
IF(SESSION("RecCount")<>"")THEN
IF(SESSION("strWhere")<>strWhere)THEN
RecCount=Conn.Execute("SELECTCOUNT("&strKeyField&")FROM"&strTable&tmpWhere)(0)
SESSION("RecCount")=RecCount
SESSION("strWhere")=strWhere
ELSE
RecCount=SESSION("RecCount")
ENDIF
ELSE
RecCount=Conn.Execute("SELECTCOUNT(*)FROM"&strTable&tmpWhere)(0)
SESSION("RecCount")=RecCount
SESSION("strWhere")=strWhere
ENDIF
IF(RecCountMODIntPageSize<>0)THEN
IntPageCount=INT(RecCount/IntPageSize)+1
ELSE
IntPageCount=RecCount/IntPageSize
ENDIF
SETCmd=Server.CreateObject("Adodb.Command")
Cmd.CommandType=4
SETCmd.ActiveConnection=Conn
Cmd.CommandText="GoalerPageSp"
Cmd.Parameters.AppendCmd.CreateParameter("@IntPageSize",4,1,4,IntPageSize)
Cmd.Parameters.AppendCmd.CreateParameter("@IntCurrPage",4,1,4,CurrPage)
Cmd.Parameters.AppendCmd.CreateParameter("@strFields",200,1,2000,strFields)
Cmd.Parameters.AppendCmd.CreateParameter("@strTable",200,1,200,strTable)
Cmd.Parameters.AppendCmd.CreateParameter("@strWhere",200,1,800,strWhere)
Cmd.Parameters.AppendCmd.CreateParameter("@strOrderType",4,1,4,strOrderType)
Cmd.Parameters.AppendCmd.CreateParameter("@strKeyField",200,1,50,strKeyField)
SETRS=Cmd.Execute()
IFRecCount<1THEN
Response.Write("没有记录")
ELSE
GetRecord=RS.GetRows(IntPageSize)
Fori=0ToUbound(GetRecord,2)
Response.Write(GetRecord(0,i),GetRecord(1,i),GetRecord(2,i))'...输出内容
NEXT
GetRecord=Null
ENDIF
SETRS=NOTHING
有用的朋友请自己慢慢调试吧，总记录是用ASP来取的，存储在SESSION里边，如果每次都统计一次总记录，将会非常费时，当然，如果你想在存储过程里来取总记录和总页数然后返回也是可以的，下边是代码：
--获取记录总数--
SET@tmpSQL='SELECT@getRecordCounts=COUNT('+@strKeyField+')FROM'+@strTable+@tmpWhere
EXECsp_executesql@tmpSQL,N'@getRecordCountsintoutput',@getRecordCountsOUTPUT
--获取总页数--
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
别忘了返回定义参数：
@getRecordCountsintoutput,--返回总记录
@getPageCountsintoutput--返回总页数
【以前写的一个分页存储过程，刚才不小心翻出来的】相关文章：