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