- Get the data of the specified page Create Procedure getRecordfromPage @tblname varchar (255), - Table name @fldname varchar (255), - field name @PageSize INT = 10, - Page size @PageIndex int = 1, - Page code @iscount bit = 0, - Return to the total number of records, non-0 values returns to @ORDERTYPE BIT = 0, - Set the sort type, non-0 value is descended @Strwhere varchar (1000) = '- Query Conditions (attention : Don't add where) AS
Declare @strsql varchar (6000) - Professional Sentences Declare @STRTMP VARCHAR (100) - Temporary Variable Declare @strORDER VARCHAR (400) - Sort Type
IF @ORDERTYPE! = 0BEGIN SET @STRTMP = "<(Select Min" set @strorder = "Order by [" @fldname "] desc" endelsebegin set @strtmp = "> (Select Max" set @strorder = "ORDER By [" @fldname "] ASC "end
Set @STRSQL = "SELECT TOP" Str (@Pagesize) "* from [" @fldname "] where [" @fldname "]" @strtmp "([" @fldname "] ) from (SELECT TOP " STR ((@ PageIndex-1) * @ PageSize) " [" @fldname "] from [" @TBLNAME "] " @strorder ") AS TBLTMP " @strorder
If @strwhere! = '' set @strsql = "select top" str (@PageSize) "* from [" @TBLNAME "] where [" @fldname "]" @strtmp "([[[ " @Fldname "]) from (SELECT TOP " STR ((@ PageIndex-1) * @ PageSize) " [" @fldname "] from [" @TBLNAME "] where " @strwhere " @Strorder ") as TBLTMP) and " @strwhere " " @strorderif @PageIndex = 1begin set @strtmp =" "if @strwhere! = '' Set @Strtmp =" Where " @strwhere
Set @strsql = "SELECT TOP" STR (@Pagesize) "* from [" @tblname "]" @strtmp "" @strorderend
IF @iscount! = 0 set @strsql = "Select count (*) as total from [" @tblname "]"
EXEC (@strsql)
Go
-------------------------------
<% @ LANGUAGE = "VBSCRIPT" CODEPAGE = "936"%> <% 'tab SQL statements generated code Function GetPageSQL (tblName, fldName, PageSize, PageIndex, OrderType, strWhere) Dim strTemp, strSQL, strOrder' generates a correlation according to the ordering Code 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 'is the unsuined statement if it is page 1, if the complex statement IF pageIndex = 1 damp =" "IF strwhere <>" "Ten Strtmp =" WHERE " STRWHERE END IF STRSQL =" SELECT TOP "& PAGESIZE &" * from ["& tblname &"] "" SQL statement strsql = "SELECT TOP" & Pagesize & STRTMP & STRORDER ELSE ' "* From [" & tblname & "] where [" & fldname & "]" & strtemp & _ "from (SELECT TOP" & (PageIndex-1) * Pagesize & "[" & FLDNAME & "] from [" & fldName & "] TBLNAME & "]" IF strwhere <> "" Then strsql = strsql & "where" & strwhere endiffsql = strsql & strOrder & ") as tblTemp)" if strWhere <> "" then strSQL = strSQL & "And" & strWhere end if strSQL = strSQL & strOrder end if GetPageSQL = strSQL 'SQL statement returns End Functionresponse.write (GetPageSQL ( "
Message "," MessageID ", 10, 1, 0,"))%> Source: http://blog.9cbs.net/sun_jianhua/archive/2004/07/04/33574.aspx
