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 in the ID. , 1, '*', 10, 2, ', @ pages outprut - Press ID 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 outprut - Press XX to take the first Three-page EXEC SP_PAGE '#T', 'DT', 2, 0, '*', 10, 2, '', @ Pages Output - Press the second page of Exec S_Page '#t', 'DT' in DT , 2, 1, '*', 10, 2, '', @ Pages Output - Press DT Reflex Sort SELECT Total Page = @ Pages
DROP TABLE #T
