以前写的一个分页存储过程，刚才不小心翻出来的

　　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.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--返回总页数