Mssql million-level paging Stored Procedure sharing

The article found two efficient paging codes for the mssqlserver stored procedure. If you need them, please refer to them.

The article found two efficient paging codes for mssql server Stored Procedures. If you need them, please refer to them.

The Code is as follows:

Create PROC P_viewPage /**//* Nzperfect [no_mIss] efficient and common stored procedure (bidirectional retrieval) 2007.5.7 QQ: 34813284 Tip: Applicable to tables or views with a single primary key or a unique value column : The SQL statement is 8000 bytes. Note that the input parameters and total SQL length must not exceed the specified range. */ @ TableName VARCHAR (200), -- table name @ FieldList VARCHAR (2000), -- displays the column name. If it is all fields, it is * @ PrimaryKey VARCHAR (100), -- single primary key or unique value Key @ Where VARCHAR (2000), -- the query condition does not contain the 'where' character, for example, id> 10 and len (userid)> 9 @ Order VARCHAR (1000), -- sorting does not contain the 'ORDER BY' character, such as id asc, userid desc. asc or desc must be specified. -- Note that it takes effect when @ SortType = 3. Remember to add the primary key at the end; otherwise, it will make you depressed. @ SortType INT, -- sorting Rule 1: Forward asc 2: reverse desc 3: Multi-column sorting method @ RecorderCount INT, -- total number of records 0: the total number of records is returned. @ PageSize INT, -- number of records output per page @ PageIndex INT, -- current page number @ TotalCount int output, -- Record total returned records @ TotalPageCount int output -- total number of returned pages AS SET NOCOUNT ON If isnull (@ TotalCount, '') ='' SET @ TotalCount = 0 SET @ Order = RTRIM (LTRIM (@ Order )) SET @ PrimaryKey = RTRIM (LTRIM (@ PrimaryKey )) SET @ FieldList = REPLACE (RTRIM (LTRIM (@ FieldList )),'','') While charindex (',', @ Order)> 0 or CHARINDEX (',', @ Order)> 0 BEGIN SET @ Order = REPLACE (@ Order ,',',',') SET @ Order = REPLACE (@ Order ,',',',') END If isnull (@ TableName, '') ='' or ISNULL (@ FieldList, '') ='' Or ISNULL (@ PrimaryKey, '') ='' Or @ SortType <1 or @ SortType> 3 Or @ RecorderCount <0 or @ PageSize <0 or @ PageIndex <0 BEGIN PRINT ('err _ 00 ') RETURN END IF @ SortType = 3 BEGIN IF (UPPER (RIGHT (@ Order, 4 ))! = 'Asc 'and upper (RIGHT (@ Order, 5 ))! = 'Desc ') Begin print ('err _ 02 ') RETURN END END DECLARE @ new_where1 VARCHAR (1000) DECLARE @ new_where2 VARCHAR (1000) DECLARE @ new_order1 VARCHAR (1000) DECLARE @ new_order2 VARCHAR (1000) DECLARE @ new_order3 VARCHAR (1000) DECLARE @ SQL VARCHAR (8000) DECLARE @ SqlCount NVARCHAR (4000) If isnull (@ where, '') ='' BEGIN SET @ new_where1 ='' SET @ new_where2 = 'where' END ELSE BEGIN SET @ new_where1 = 'where' + @ Where SET @ new_where2 = 'where' + @ Where + 'and' END If isnull (@ order, '') ='' or @ SortType = 1 or @ SortType = 2 BEGIN IF @ SortType = 1 BEGIN SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'asc' SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'desc' END IF @ SortType = 2 BEGIN SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'desc' SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'asc' END END ELSE BEGIN SET @ new_order1 = 'ORDER BY' + @ orDER END IF @ SortType = 3 and charindex (',' + @ PrimaryKey + '', ',' + @ Order)> 0 BEGIN SET @ new_order1 = 'ORDER BY' + @ orDER SET @ new_order2 = @ Order + ',' SET @ new_order2 = REPLACE (@ new_order2, 'asc, ',' {ASC}, '), 'desc',' {DESC },') SET @ new_order2 = REPLACE (@ new_order2, '{ASC},', 'desc'), '{DESC},', 'asc ,') SET @ new_order2 = 'ORDER BY' + SUBSTRING (@ new_order2, 1, LEN (@ new_order2)-1) IF @ FieldList <> '*' BEGIN SET @ new_order3 = REPLACE (@ Order + ',', 'asc, ','), 'desc ,',',') SET @ FieldList = ',' + @ FieldList While charindex (',', @ new_order3)> 0 BEGIN If charindex (SUBSTRING (',' + @ new_order3, 1, CHARINDEX (',', @ new_order3), ',' + @ FieldList + ',')> 0 BEGIN SET @ FieldList = @ FieldList + ',' + SUBSTRING (@ new_order3, 1, CHARINDEX (',', @ new_order3 )) END SET @ new_order3 = SUBSTRING (@ new_order3, CHARINDEX (',', @ new_order3) + 1, LEN (@ new_order3 )) END SET @ FieldList = SUBSTRING (@ FieldList, 2, LEN (@ FieldList )) END END SET @ SqlCount = 'select @ TotalCount = COUNT (*), @ TotalPageCount = CEILING (COUNT (*) + 0.0 )/' + CAST (@ PageSize as varchar) + ') FROM (Select * FROM' + @ TableName + @ new_where1 + ') AS t' IF @ RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL @ SqlCount, n' @ TotalCount int output, @ TotalPageCount INT output ', @ TotalCount OUTPUT, @ TotalPageCount OUTPUT END ELSE BEGIN Select @ TotalCount = @ RecorderCount END IF @ PageIndex> CEILING (@ TotalCount + 0.0)/@ PageSize) BEGIN SET @ PageIndex = CEILING (@ TotalCount + 0.0)/@ PageSize) END IF @ PageIndex = 1 or @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) BEGIN IF @ PageIndex = 1 -- returns the first page of data BEGIN SET @ SQL = 'select * FROM (Select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp' + @ new_order1 END IF @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) -- returns the last page of data BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select top' + STR (ABS (@ PageSize * @ PageIndex-@ TotalCount-@ PageSize )) + ''+ @ FieldList + 'from' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp' + @ New_order1 END END ELSE BEGIN IF @ SortType = 1 -- Only sort the primary key in positive order BEGIN IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(Select MAX (' + @ PrimaryKey + ') FROM (Select TOP' + STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'From' + @ TableName + @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1 END ELSE -- reverse Retrieval BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select TOP '+ STR (@ PageSize) +'' + @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(Select MIN (' + @ PrimaryKey + ') FROM (Select TOP' + STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'From' + @ TableName + @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2 + ') As tmp' + @ new_order1 END END IF @ SortType = 2 -- Only sort the primary key in reverse order BEGIN IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '<' + '(Select MIN (' + @ PrimaryKey + ') FROM (Select TOP' + STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey + 'From' + @ TableName + @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1 END ELSE -- reverse Retrieval BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select TOP '+ STR (@ PageSize) +'' + @ FieldList + 'from' + @ TableName + @ new_where2 + @ PrimaryKey + '>' + '(Select MAX (' + @ PrimaryKey + ') FROM (Select TOP' + STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey + 'From' + @ TableName + @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2 + ') As tmp' + @ new_order1 END END IF @ SortType = 3 -- Multi-column sorting, which must contain the primary key and be placed at the end; otherwise, it is not processed. BEGIN If charindex (',' + @ PrimaryKey + ',', '+ @ Order) = 0 Begin print ('err _ 02 ') RETURN END IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select top' + STR (@ PageSize * @ PageIndex) + ''+ @ FieldList + 'From' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp' + @ New_order2 + ') as tmp' + @ new_order1 END ELSE -- reverse Retrieval BEGIN SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from (' + 'Select top' + STR (@ TotalCount-@ PageSize * @ PageIndex + @ PageSize) + ''+ @ FieldList + 'From' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp' + @ New_order1 + ') as tmp' + @ new_order1 END END END PRINT (@ SQL) EXEC (@ SQL)

Write your own

The Code is as follows:

USE [CaiLi] GO /***** Object: StoredProcedure [dbo]. [SqlPagination] Script Date: 10/26/2011 11:40:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create procedure [dbo]. [SqlPagination] /* **************************************** *********************** ** Tens of millions of paging stored procedures ** **************************************** *********************** Parameter description: 1. Tables: Table Name, View 2. PrimaryKey: Primary Key 3. Sort: Sorting statement without Order By such as NewsID Desc and OrderRows Asc 4. CurrentPage: Current page number 5. PageSize: page size 6. Filter: Filter statement without Where 7. Group: Group statement without Group **************************************** ***********************/ ( @ Tables varchar (1000 ), PrimaryKey varchar (100 ), @ Sort varchar (200) = NULL, @ CurrentPage int = 1, @ PageSize int = 10, @ Fields varchar (1000) = '*', @ Filter varchar (1000) = NULL, @ Group varchar (1000) = NULL ) AS /* Default sorting */ If @ PrimaryKey is null or @ PrimaryKey ='' Set @ PrimaryKey = 'id' IF @ Sort is null or @ Sort ='' SET @ Sort = @ PrimaryKey IF @ Fields is null or @ Fields ='' SET @ Fields = '*' DECLARE @ SortTable varchar (100) DECLARE @ SortName varchar (100) DECLARE @ strSortColumn varchar (200) DECLARE @ operator char (2) DECLARE @ type varchar (100) DECLARE @ prec int /* Set the sorting statement .*/ If charindex (',', @ Sort)> 0 Set @ strSortColumn = substring (@ Sort, 0, charindex (',', @ Sort )) Else Set @ strSortColumn = @ Sort If charindex ('desc', @ Sort)> 0 BEGIN SET @ strSortColumn = REPLACE (@ strSortColumn, 'desc ','') SET @ operator = '<=' END ELSE BEGIN If charindex ('asc ', @ Sort)> 0 BEGIN SET @ strSortColumn = REPLACE (@ strSortColumn, 'asc ','') SET @ operator = '> =' END END If charindex ('.', @ strSortColumn)> 0 BEGIN SET @ SortTable = SUBSTRING (@ strSortColumn, 0, CHARINDEX ('.', @ strSortColumn )) SET @ SortName = SUBSTRING (@ strSortColumn, CHARINDEX ('.', @ strSortColumn) + 1, LEN (@ strSortColumn )) END ELSE BEGIN SET @ SortTable = @ Tables SET @ SortName = @ strSortColumn END Select @ type = t. name, @ prec = c. prec FROM sysobjects o JOIN syscolumns c on o. id = c. id JOIN policypes t on c. xusertype = t. xusertype Where o. name = @ SortTable AND c. name = @ SortName If charindex ('Char ', @ type)> 0 SET @ type = @ type + '(' + CAST (@ prec AS varchar) + ')' DECLARE @ strPageSize varchar (50) DECLARE @ strStartRow varchar (50) DECLARE @ strFilter varchar (1000) DECLARE @ strSimpleFilter varchar (1000) DECLARE @ strGroup varchar (1000) DECLARE @ strSort varchar (200) /* Default current page */ IF @ CurrentPage <1 SET @ CurrentPage = 1 /* Set paging parameters .*/ SET @ strPageSize = CAST (@ PageSize AS varchar (50 )) SET @ strStartRow = CAST (@ CurrentPage-1) * @ PageSize + 1) AS varchar (50 )) /* Filter and group statement .*/ IF @ Filter is not null and @ Filter! ='' BEGIN SET @ strFilter = 'where 1 = 1' + @ Filter +'' SET @ strSimpleFilter = @ Filter +'' END ELSE BEGIN SET @ strSimpleFilter ='' SET @ strFilter ='' END IF @ Group is not null and @ Group! ='' SET @ strGroup = 'group by' + @ GROUP +'' ELSE SET @ strGroup ='' IF @ Sort is not null and @ Sort! ='' SET @ strSort = 'ORDER BY' + @ Sort +'' ELSE SET @ strSort ='' -- Print ('select' + @ Fields + 'from' + '(Select *, ROW_NUMBER () OVER (' + @ strSort + ') as RowNumber FROM '+ @ Tables +') t' + 'where t. rowNumber between '+ @ strStartRow +' and '+ ''+ @ strSimpleFilter +'' + @ strSort + @ strGroup) /* Execute the query statement */ Declare @ STRORDER varchar (50) If CHARINDEX (',', @ strSort)> 0 Set @ STRORDER = SUBSTRING (@ strSort, 0, CHARINDEX (',', @ strSort )) Else Set @ STRORDER = @ strSort EXEC ( 'Destare @ SortColumn '+ @ type +' DECLARE @ TotalCount int DECLARE @ ENDCOUNT int DECLARE @ strENDCOUNT varchar (50) -- Select count (1) FROM '+ @ Tables + @ strFilter +' Set @ TotalCount = (Select count (1) FROM '+ @ Tables + @ strFilter +') '+' Set rowcount '+ @ strStartRow +' SET @ ENDCOUNT = CAST ('+ @ strStartRow +' AS int) + CAST ('+ @ strPageSize +' AS int)-1 IF @ ENDCOUNT> @ TotalCount BEGIN SET @ ENDCOUNT = @ TotalCount END Set @ strENDCOUNT = CAST (@ endcount as varchar (50 )) Select @ SortColumn = '+ @ strSortColumn + 'from' + @ Tables + @ strFilter + ''+ @ strGroup + @ strSort +' Set rowcount '+ @ strPageSize +' Select '+ @ Fields + 'from' +' (Select *, ROW_NUMBER () OVER ('+ @ STRORDER + ') as RowNumber FROM '+ @ Tables + 'where 1 = 1' + @ strSimpleFilter +') t' + 'where t. rowNumber between '+ @ strStartRow +' and @ strENDCOUNT '+ @ strGroup + @ strSort + '') GO