【转】ASP构造大数据量的分页SQL语句

本文转自：http://hi.baidu.com/gotodbz/blog/item/b8777e1684ad681c972b43c4.html

1<%@Language = "VBScript" Codepage = "936"%>
2<%
3'分页sql语句生成代码
4Function GetPageSql(TblName,FldName,PageSize,PageIndex,OrderType,StrWhere)
5     Dim StrTemp,StrSql,StrOrder
6     '根据排序方式生成相关代码
7     If OrderType = 0 Then
8         StrTemp = "> (Select Max([" & FldName & "])"
9         StrOrder = " Order By [" & FldName & "] Asc"
10     Else
11         StrTemp = "< (Select Min([" & FldName & "])"
12         StrOrder = " Order By [" & FldName & "] Desc"
13     End If
14    
15     '若是第1页则无须复杂的语句
16     If PageIndex = 1 Then
17         StrTemp = ""
18         If StrWhere <> "" Then
19             Strtmp = " Where " & StrWhere
20         End If
21         StrSql = "Select Top " & PageSize & " * From [" & TblName & "]" & Strtmp & StrOrder
22     Else         '若不是第1页，构造sql语句
23         StrSql = "Select Top " & PageSize & " * From [" & TblName & "] Where [" & FldName & "]" & StrTemp & _
24         " From (Select Top " & (PageIndex-1) * PageSize & " [" & FldName & "] From [" & TblName & "]"
25         If StrWhere <> "" Then
26             StrSql = StrSql & " Where " & StrWhere
27         End If
28         StrSql = StrSql & StrOrder & ") As Tbltemp)"
29         If StrWhere <> "" Then
30             StrSql = StrSql & " And " & StrWhere
31         End If
32         StrSql = StrSql & StrOrder
33     End If
34     GetPageSql = StrSql         '返回sql语句
35End Function
36Response.Write(GetPageSql("Message","MessageID",10,1,0,""))
37%>