MSSQL paging Stored Procedure

Version1.0.54.04.07.15 update this stored procedure is a paging stored procedure that is frequently used on the network after the paging stored procedure is rewritten. It has high efficiency, but does not guarantee the highest efficiency. This stored procedure is written on MSSQL2012 and is not guaranteed to be compatible with all versions of MSSQL (it is known that MSSQL2005 needs to modify a small amount of code ). Not compatible with other databases. Ben

Version1.0.54.04.07.15 update this stored procedure is a paging stored procedure that is frequently used on the network after the paging stored procedure is rewritten. It has high efficiency, but does not guarantee the highest efficiency. This stored procedure is written on MSSQL2012 and is not guaranteed to be compatible with all versions of MSSQL (it is known that MSSQL2005 needs to modify a small amount of code ). Not compatible with other databases. Ben

Version 1.0.5 updated on January 15
This stored procedure is a paging stored procedure that is frequently used on the network and rewritten after the paging stored procedure. It is highly efficient, but it is not guaranteed to be the most efficient.
This stored procedure is written on MSSQL2012 and is not guaranteed to be compatible with all versions of MSSQL (it is known that MSSQL2005 needs to modify a small amount of code ). Not compatible with other databases.
This paging stored procedure only supports common SQL statements.
If you find problems, please contact ttio4116@live.com or to my personal blog http://blog.ttionya.com message, make progress together !!

For more information, see README in the file. <无>

? SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/* READMEVersion 1.0.5 this stored procedure is a paging stored procedure that I searched and compared to the paging Stored Procedure frequently used on the network. It has high efficiency, but does not guarantee the highest efficiency. This stored procedure is written on MSSQL2012 and is not guaranteed to be compatible with all versions of MSSQL (it is known that MSSQL2005 needs to modify a small amount of code ). Not compatible with other databases. This paging stored procedure only supports common SQL statements. If you find problems, please contact ttio4116@live.com, make progress together !! Version update: Version 1.0.1: fixed the bug where the wrong SQL statement was used to query the first page of data. Version 1.0.2: updated the interpretation of some parameters. Version 1.0.3: fixed an error caused BY a table name error during multi-Table query. Version 1.0.4: removed some unnecessary conditional judgment statements, Version 1.0.5: fixed the group by error on the first page, now you can add COUNT (), MAX (), and so on (not limited to) Aggregate functions in @ FldName. Note: 1. the HAVING statement must be used with the group by statement in the format of group by xxx having xxx. 2. @ FldSort set ASC or DESC for the fields to be sorted. 3. @ strOrder is the sorting & aggregation parameter. It takes effect when @ FldSort is not set during sorting (by default, it is in the forward order). to calculate the total number of items during aggregation, if this parameter is null, take the first field of @ FldSort. 4. It is recommended that @ strOrder be set as the primary key, even if it is not the primary key, do not include NULL, otherwise unexpected results will occur. If this parameter is NULL, make sure that the first field of @ FldSort does not contain NULL. */Create procedure [dbo]. [MyPageRead] (@ TblName nvarchar (3000) -- Name of the connected table, that is, the content following FROM, @ FldName nvarchar (3000) = '*' -- Name of the field to be queried, the default value is "all". @ FldSort nvarchar (3000) = NULL -- sorting field. order by is not required. Set sorting BY yourself. Add ASC or DESC and @ strCondition nvarchar (3000) = NULL -- the statement to be queried, WHERE is not required, and or is not required before, but does not affect the calculation. @ strGroup nvarchar (3000) = NULL -- the statement to be aggregated, no need for group by, @ strHaving nvarchar (3000) = NULL -- HAVING statement, no HAVING, @ Dist bit = 0 -- whether to remove duplicate data, 0 does not remove/1 Remove, @ strOrder nvarchar (1000) = NULL -- A sorting field, which must be specified when @ FldSort is empty. This field is used to calculate the total number of items. If this field is null, select the first field of @ FldSort, and @ OnlyCounts bit = 0 -- whether to return only the total number of items without paging, @ PageSize int = 10 -- the number of items to be displayed on each Page, @ Page int = 1 -- the data on the Page to be displayed, @ Counts int = 1 output -- The total number of items returned, @ PageCounts int = 1 output -- total number of returned pages) asset nocount on -- no count -- Definition variable DECLARE @ tmpFldSort nvarchar (3000) -- DECLARE @ tmpstrCondition nvarchar (3000) -- DECLARE @ tmpstrGroup nvarchar (3000) -- DECLARE @ tmpstrGroup nvarchar () -- DECLARE @ tmpstrfirst nvarchar ((3000) -- 1. control DistDECLARE @ tmpstrfirstCount nvarchar (3000) -- 2. control Dist/* calculation time */DECLARE @ StartTime datetimeSET @ StartTime = GETDATE () IF (@ FldSort is null or @ FldSort = '') AND (@ strOrder is null or @ strOrder = '') RETURN -- there must be a value. If there IS a problem, directly jump out of IF @ FldSort is null or @ FldSort = ''SET @ tmpFldSort = @ strOrder + 'asc 'elseset @ tmpFldSort = @ FldSort -- the preceding command sets the order by statement IF @ strCondition IS null or @ strCondition = ''SET @ TmpstrCondition = ''elsebeginif CHARINDEX ('and', LTRIM (@ strCondition) = 1 SET @ strCondition = RIGHT (@ strCondition, LEN (@ strCondition)-4) if charindex ('OR', LTRIM (@ strCondition) = 1 SET @ strCondition = RIGHT (@ strCondition, LEN (@ strCondition)-3) SET @ tmpstrCondition = 'where' + @ strConditionEND -- SET the WHERE statement IF @ strGroup is null or @ strGroup = ''SET @ tmpstrGroup ='' ELSEBEGINSET @ tmpstrGroup = 'group '+ @ strGroupIF @ s TrHaving is not null and @ strHaving <> ''set @ tmpstrGroup = @ tmpstrGroup + 'having '+ @ strHavingEND -- SET the group by statement DECLARE @ tmpFldsubstr nvarchar (1000) -- the first field of sorting, used to calculate the total data volume, valid when @ strOrder has no data IF @ Dist = 0 BEGINSET @ tmpstrfirst = 'select' IF @ strOrder is null or @ strOrder = ''BEGINSET @ tmpFldsubstr = LEFT (LTRIM (@ FldSort ), CHARINDEX (CHAR (32), LTRIM (@ FldSort) SET @ tmpstrfirstCount = 'select @ Counts = COUNT ('+ @ tmpFldsubst R + ') 'endelseset @ tmpstrfirstCount = 'select @ Counts = COUNT (' + @ strOrder + ') 'endelsebeginset @ tmpstrfirst = 'select DISTINCT 'IF @ strOrder is null or @ strOrder = ''ininset @ tmpFldsubstr = LEFT (LTRIM (@ FldSort), CHARINDEX (CHAR (32 ), LTRIM (@ FldSort) SET @ tmpstrfirstCount = 'select @ Counts = COUNT (DISTINCT '+ @ tmpFldsubstr + ') 'endelseset @ tmpstrfirstCount = 'select @ Counts = COUNT (DISTINCT '+ @ strOrder +') 'END -- the above is set through @ Dist @ CountsDECLARE @ sqlStr nvarchar (3000) -- query the SQL statement IF @ tmpstrGroup = ''SET @ sqlStr = @ tmpstrfirstCount + 'from' + @ TblName + @ tmpstrConditionELSEBEGINSET @ tmpstrfirstCount = REPLACE (@ tmpstrfirstCount, '@ Counts =', '') SET @ sqlStr = 'select @ Counts = COUNT (*) FROM ('+ @ tmpstrfirstCount +' AS tmpF from' + @ TblName + @ tmpstrCondition + @ tmpstrGroup + ') AS tmpt' ENDEXEC sp_executesql @ sqlStr, n' @ Counts int out ', @ Counts out -- returns the query result IF @ OnlyCounts = 1RETURN -- IF @ OnlyCounts = 1, return the total number of items directly DECLARE @ tmpCounts int IF @ Counts = 0 SET @ tmpCounts = 1 else set @ tmpCounts = @ CountsSET @ PageCounts = (@ tmpCounts + @ PageSize-1) /@ PageSize -- get the total number of pages above IF @ Page <1 SET @ Page = 1IF @ Page> @ PageCountsSET @ Page = @ PageCounts -- SET the Page DECLARE @ tmpsql nvarchar (3000) -- set the final SQL statement to be queried IF @ Page = 1 -- when the first Page is retrieved, use the fastest algorithm SET @ tmpsql = @ tmpstrfirst + 'top' + CAST (@ PageSize AS nvarchar (50) +' '+ @ FldName + 'from' + @ TblName + @ tmpstrCondition + @ tmpstrGroup + 'ORDER BY' + @ tmpFldSortIF @ Page> 1 AND @ Page <= @ PageCounts/2 -- this is the Page to be queried is in the first half of the total number of pages BEGINSET @ tmpsql = 'WITH temptbl AS (select top' + CAST (@ Page * @ PageSize AS nvarchar (50 )) + 'row_number () OVER (order by '+ @ tmpFldSort +') AS tmpRowIndex, '+ @ FldName + 'from' + @ TblName + ''+ @ tmpstrCondition +'' + @ tmpstrGroup +') 'SET @ tmpsql = @ tmpsql + 'select * FROM temptbl WHER E [tmpRowIndex] between' + CAST (@ Page-1) * @ PageSize + 1 AS nvarchar (50) + 'and' + CAST (@ Page-1) * @ PageSize + @ PageSize AS nvarchar (50 )) ENDIF @ Page> 1 AND @ Page> @ PageCounts/2 -- query the data in the second half of the total number of pages. BEGINSET @ tmpFldSort = REPLACE (@ tmpFldSort, 'asc ','[~ 1] ') SET @ tmpFldSort = REPLACE (@ tmpFldSort, 'desc ','[~ 2] ') SET @ tmpFldSort = REPLACE (@ tmpFldSort ,'[~ 1] ', 'desc') SET @ tmpFldSort = REPLACE (@ tmpFldSort ,'[~ 2] ', 'asc') -- optimizes the half-end data query and swaps the query conditions ,[~ 1] DESC ,[~ 2] for ASCSET @ tmpsql = 'WITH temptbl AS (select top' + CAST (@ Counts-(@ Page-1) * @ PageSize AS nvarchar (50 )) + 'row_number () OVER (order by '+ @ tmpFldSort +') AS tmpRowIndex, '+ @ FldName + 'from' + @ TblName + ''+ @ tmpstrCondition +'' + @ tmpstrGroup + ') 'set @ tmpsql = @ tmpsql + 'select * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+ CAST (@ Counts-(@ Page-1) * @ PageSize + @ PageSize-1) AS nvarchar (50) + 'and' + CAST (@ Counts-(@ Page-1) * @ PageSize) AS nvarchar (50 )) + 'order BY tmpRowIndex DESC 'END -- SELECT @ tmpsql view the spliced string EXEC sp_executesql @ tmpsql/* calculate the time */-- select datediff (MS, @ StartTime, GETDATE ()) AS [Time]/**/SET NOCOUNT OFF