Procedure 1:
Create Procedure SP_Page @tb varchar (50), - Table name @col varchar (50), - Page @coltype int, - @ col column, 0- characters, 1-character type 2-date Time Type @ORDERBY BIT, - Sort, 0 - Order, 1-Reverse @Collist Varchar (800), - To query the list of fields, * Represents all field @PageSize Int, - per page record Number @Page Int, - Specify page @condition varchar (800), - Query Conditions @Pages Int Output - Total Page AS / * Function Description: Sub-query in the specified list, Page can be sequential, reverse query can specify page size, specify the query of any page, specify the list of output fields, return to the total number of pages: PBSQL version: 1.10 Last modified: 2004-11-29 * / declare @sql nvarchar (4000) , @ WHERE1 VARCHAR (800), @ WHERE2 VARCHAR (800) IF @Condition IS NULL OR RTRIM (@condition) = '' Begin - No Query Conditions Set @ WHERE1 = 'Where' set @ where2 = 'endelsebegin - Query condition set @ where1 = 'where (' @ condition ') and' - Conditional Conditions plus this condition set @ WHERE2 = 'Where (' @ condition ')' - there is no condition and plus This condition endset @ SQL = 'SELECT @ Pages = CEILING ((*) 0.0) /' CAST (@Pagesize As Varchar) ') from' @ TB @ where2exec sp_executesql @ SQL, N '@ Pages Int Output ', @ Pages Output - Computing Total Page IF @ OrderBy = 0 Set @ SQL =' SELECT TOP ' Cast (@PageSize as varchar) ' @ Collist 'from' @ TB @ WHERE1 @ col '> (SELECT MAX (' @ col ')' 'from (SELECT TOP' CAST (@PageSize * (@ Page-1) as varchar) '' @ col 'from' @ TB @ WHERE2 'Order By' @
COL ') T) Order by' @ Colelse Set @ SQL = 'SELECT TOP' CAST (@Pagesize As Varchar) '' @ Collist 'from' @ TB @ WHERE1 @ Col <(SELECT MIN) @ col ')' 'from (SELECT TOP' CAST (@PageSize * (@PAGE-1) as varchar) '' @ col 'from' @ TB @ WHERE2 ORDER BY ' @ COL ' DESC) T) ORDER BY ' @ Col ' Desc'IF @ Page = 1 - First Set @ SQL = 'SELECT TOP' CAST (@Pagesize As Varchar) '' @ Collist 'from' @ TB @ WHERE2 'ORDER BY' @ Col Case @ORDERBY WHEN 0 THEN '' ELSE 'DESC' Endexec (@SQL) GO This memory process is efficient, with 5 million data test (established index), only return paging Just 3 seconds, where the effect affects the total number of pages, if you don't need to comment out - Test sample Declare @Pages Intselect Identity (int, 1, 1) ID, getdate () DT, XX = Cast ('' As VARCHAR (10)) INTO #T from sysobjectsupdate #t set dt = dateadd (days, ID-200, dt), xx = 'xxxx' Right ('000000' Cast (ID As Varchar (10)), 6 EXEC SP_PAGE '#T', 'ID', 0, 0, '*', 10, 2, '', @ Pages Output - Press the second page of EXEC SP_PAGE '#t', 'ID', 0, 1, '*', 10, 2, ', @ pages output - pressing the second page of EXEC SP_PAGE' #t ',' XX ', 1, 0,' * ', 10, 3, ', @ Pages Output - Press XX to take the third page exec sp_page' #t ',' xx ' , 1, 1, '*', 10, 3, '', @ Pages Output - Press XX to refer to EXEC SP_PAGE '#T', 'DT', 2, 0, '*', 10, 2 , '', @
Pages Output - Press the second page of Exec sp_page '#t', 'DT', 2, 1, '*', 10, 2, '', @ pages output - Press DT. Total page = @ Pages Drop Table #t Procedure 2: Create Procedure [DBO]. [GetPageData] @fieldsname nvarchar (4000), - Field Name @tablename NVARCHAR (500), - Name @Condition NVARCHAR (4000) , @ Sort nvarchar (4000), - Sort @PageSize Int = 10, @ Pagenum INT = 1 OUT, @ @Power Int = 0 out, @ Debug nvarchar (4000) Outasif @Condition! = ' 'Set @ condition =' where ' @ conditionif @Sort! =' Set @ Sort = 'Order by' @ sortelsebegin set @ debug = 'Sort by wrong FIELDS [ASC | DESC]' ReturnendDeclare @Sqlstr NVARCHAR (4000) - Profile Declare @unsort nvarchar (4000) Declare @count intDeclare @LastCount Intset @ unsort = Replace (@ sort, 'asc', 'xs') set @ unsort = Replace (@ unsort,' dec ", 'ASC' ) set @ UNSORT = Replace (@ unsort, 'xs', 'dec ") set @ SQLSTR =' select @ count = count (*) from ' @ Tablename ' ' @ ConditionExec sp_executesql @ SQLSTR, N' @ Count int INT Out ', @ count out set @ rowcount = @ countset @ pagecount = @ count / @ Pagesizset @ lastcount = @ count% @ Pagesizeif @LastCount> 0 Begin set @PAGECou NT = @ PageCount 1ENDIF (@PAGENUM <= 0) Begin Set @ Pagenum = 1ENDIF ((@ Pagenum * @ Pagesize)> = @ rowcount) Begin Set @ Pagenum = @ PageCountendif ((@ Pagenum * @ Pagesize)> = @ Rowcount) and (@lastcount> 0) begin set @ SQLSTR = '
