9CBS - Document Center - Database Developer reading: 1232 Comments: 0 Participation in Reviews
Title One of the stored procedures of the data pagination Select the BLOG key of PBSQL a stored procedure of the data pagination Create Procedure sp_page @tb varchar (50), - Table name @col varchar (50), - press this column Paging @ColType int, - @ col column type, 0-digital type, 1-character type, 2-date Time Type @ORDERBY BIT, - Sort, 0 - Order, 1-Reverse @Collist Varchar (800) ,- To query the list list, * Represents all field @Pagesize Int, - Number per page @Page Int, - Specify page @Pages Int Output - Total Page AS / * Function Description: The records that meet the conditions in the specified table are paid by the specified column. Page can be sequentially, the collapse query can specify the page size, specify the query of any page, specify the output field list, return to the total number of pieces : 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 Conditions set @ where2 = 'where (' @ condition ')' - there is no condition and this condition endset @ SQL = 'select @ Pages = CEILING (count (*) 0.0) /' cast (@ Pagesize as varchar) ') from' @ TB @ where2exec sp_executesql @ SQL, N '@ Pages Int OUTP UT ', @ 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 'dec) T) ORDER BY' @ Col 'Desc'IF @ Page = 1 - 1 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 3 seconds, only 3 seconds, the way affecting efficiency is calculating 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 (day, 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 in order to take the second page EXEC Sp_page '#t', 'ID', 0, 1, '*', 10, 2, '', @ Pages Output - Press ID Equation 2 EXEC SP_PAGE '#T', 'XX', 1, 0, '*', 10, 3, ', @ Pages Output - Press XX Order Page 3 EXEC SP_PAGE '#T', 'XX', 1, 1, '*', 10, 3, '', @ Pages Output - Press XX Reflex to take the third page EXEC SP_PAGE '#T', '
