ASP constructs paging SQL statements with large data volumes

This article from: http://hi.baidu.com/gotodbz/blog/item/b8777e1684ad681c972b43c4.html

1 <% @ Language = "VBScript" codePage = "936" %> 2 <% 3 'Code generated using paging SQL statements 4 function getpagesql (tblname, fldname, pagesize, pageindex, ordertype, strwhere) 5 Dim strtemp, strsql, strorder 6' generate relevant code based on the sorting method 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' complex statements are not required for 1st pages 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 'if the page is not 1st, construct an SQL statement 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' return the SQL statement 35end Function 36response. Write (getpagesql ("message", "messageid", 10, 1, 0 ,"")) 37%>