ASP构造大数据量的分页SQL语句。

看了铁拳的存储过程“实现千万级数据的分页显示！”，觉得不错，我给改成了ASP代码了。

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>

<%

'分页SQL语句生成代码

Function GetPageSQL(tblName,fldName,PageSize,PageIndex,OrderType,strWhere)

Dim strTemp,strSQL,strOrder

'根据排序方式生成相关代码

if OrderType=0 then

strTemp=">(select max([" & fldName & "])"

strOrder=" order by [" & fldName & "] asc"

else

strTemp="<(select min([" & fldName & "])"

strOrder=" order by [" & fldName & "] desc"

end if

'若是第1页则无须复杂的语句

if PageIndex=1 then

strTemp=""

if strWhere<>"" then

strTmp = " where " + strWhere

end if

strSQL = "select top " & PageSize & " * from [" & tblName & "]" & strTmp & strOrder

else '若不是第1页，构造SQL语句

strSQL="select top " & PageSize & " * from [" & tblName & "] where [" & fldName & "]" & strTemp & _

" from (select top " & (PageIndex-1)*PageSize & " [" & fldName & "] from [" & tblName & "]"

if strWhere<>"" then

strSQL=strSQL & " where " & strWhere

end if

strSQL=strSQL & strOrder & ") as tblTemp)"

if strWhere<>"" then

strSQL=strSQL & " And " & strWhere

end if

strSQL=strSQL & strOrder

end if

GetPageSQL=strSQL '返回SQL语句

End Function

response.write(GetPageSQL("message","messageid",10,1,0,""))

%>