SQL Server paging stored procedures, SQL stored procedures

SQL Server paging stored procedures, SQL stored procedures

Paging storage process 1:

--/* ----- Stored Procedure paging processing Sun Wei creation ------- */--/* ----- processed the data in two minutes so that the performance of the first half of the query data is the same as that of the second half of the query -------* /--/* ----- Stored Procedure paging processing Sun Wei modify add Distinct query function ------- */--/* ----- Stored Procedure paging processing Sun Wei modify multi-field sorting rules -------*/--/ * ----- Stored Procedure paging processing Sun Wei modify multi-field sorting modification ------- */--/* ----- Stored Procedure paging processing Sun Wei modify data paging mode to top max mode performance has greatly improved ------- */--/* ----- disadvantages: the primary key of the not in version can only be an integer field. If the primary key is of the GUID type, use the not in mode version ------- */create procedure dbo. proc_ListPageInt (@ tblName nvarchar (200), ---- connection to the table or multiple tables to be displayed @ fldName nvarchar (500) = '*', ---- list of fields to be displayed @ pageSize int = 10, ---- number of records displayed on each page @ page int = 1, ---- records on the page to be displayed @ pageCount int = 1 output, ---- total number of pages after the query result pagination @ Counts int = 1 output, ---- number of records queried @ fldSort nvarchar (200) = null, ---- sorting field list or condition @ Sort bit = 0, ---- sorting method, 0 is ascending, 1 is in descending order (for multi-field sorting, Sort refers to the sorting order of the last sorting field (the last sorting field is not marked with a sorting mark)-the Program transmits the parameter as follows: 'sorta Asc, sortB Desc, SortC ') @ strCondition nvarchar (1000) = null, ---- query condition, where @ ID nvarchar (150) is not required ), ---- primary key of the primary Table @ Dist bit = 0 ---- whether to add the query field DISTINCT 0 by default. no/1 is added.) as set nocount on Declare @ sqlTmp nvarchar (1000) ---- store the dynamically generated SQL statement Declare @ strTmp nvarchar (1000) ---- store the query statement Declare @ strID nvarchar (1000) that obtains the total number of query results) ---- Declare @ strSortType nvarchar (10)-data sorting Rule A Declare @ strFSortType nvarchar (10) ---- data sorting rule B Declare @ SqlSelect nvarchar (50) ---- construct SQL statements for queries containing DISTINCT Declare @ SqlCounts nvarchar (50) ---- SQL construction of the total number of queries containing DISTINCT if @ Dist = 0 begin set @ SqlSelect = 'select' set @ SqlCounts = 'count (*) 'End else begin set @ SqlSelect = 'select distinct 'set @ SqlCounts = 'count (DISTINCT' + @ ID + ') 'End if @ Sort = 0 begin set @ strFSortType = 'asc' set @ strSortType = 'desc' end else begin set @ strFSortType = 'desc' set @ strSortType = 'asc' end -------- generate query statement -------- -- Here @ strTmp is the statement for obtaining the number of query results if @ strCondition is null or @ strCondition = ''-- no display condition is set begin set @ sqlTmp = @ fldName + 'from' + @ tblName set @ strTmp = @ SqlSelect + '@ Counts =' + @ SqlCounts + 'from' + @ tblNameset @ strID = 'from' + @ tblName end else begin set @ sqlTmp = + @ fldName + 'from' + @ tblName + 'where (1> 0) '+ @ strCondition set @ strTmp = @ SqlSelect +' @ Counts = '+ @ SqlCounts + 'from' + @ tblName + 'where (1> 0) '+ @ strCondition set @ strID = 'from' + @ tblName + 'where (1> 0)' + @ strCondition end ---- get the total number of query results ----- exec sp_executesql @ strTmp, n' @ Counts int out ', @ Counts out declare @ tmpCounts int if @ Counts = 0 set @ tmpCounts = 1 else set @ tmpCounts = @ Counts -- get the total number of pages set @ pageCount = (@ tmpCounts + @ pageSize-1) /@ pageSize/** // ** obtain the last page when the current page is greater than the total page number **/if @ page> @ pageCount set @ page = @ pageCount --/* ----- data page 2-point processing ------- */declare @ pageIndex int -- total number/page size declare @ lastcount int -- total number % page size set @ pageIndex = @ tmpCounts/@ pageSize set @ lastcount = @ tmpCounts % @ pageSize if @ lastcount> 0 set @ pageIndex = @ pageIndex + 1 else set @ lastcount = @ pagesize -- // *** display pagination if @ strCondition is null or @ strCondition =' '-- the display condition begin if @ pageIndex <2 or @ page <= @ pageIndex/2 + @ pageIndex % 2 -- the First Half of data processing is begin if @ page = 1 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'ORDER BY' + @ fldSort +'' + @ strFSortType else begin set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ ID + '<(select min (' + @ ID + ') from ('+ @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName + 'ORDER BY' + @ fldSort +'' + @ strFSortType + ') AS TBMinID) '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType end else begin set @ page = @ pageIndex-@ page + 1 -- data processing in the second half if @ page <= 1 -- display the last page of data: set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ lastcount as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'ORDER BY' + @ fldSort +'' + @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType else set @ strTmp = @ SqlSelect +' * from ('+ @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ ID + '> (select max (' + @ ID + ') from ('+ @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName + 'ORDER BY' + @ fldSort +'' + @ strSortType + ') AS TBMaxID) '+ 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType end else -- Query condition begin if @ pageIndex <2 or @ page <= @ pageIndex/2 + @ pageIndex % 2 -- data processing for the first half is begin if @ page = 1 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where 1 = 1' + @ strCondition + 'ORDER BY' + @ fldSort +'' + @ strFSortType else begin set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ ID + '<(select min (' + @ ID + ') from ('+ @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strFSortType +') AS TBMinID) '+ ''+ @ strCondition + 'ORDER BY' + @ fldSort +'' + @ strFSortType end else begin set @ page = @ pageIndex-@ page + 1 -- data processing in the second half if @ page <= 1 -- set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ lastcount as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType else set @ strTmp = @ SqlSelect +' * from ('+ @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4 )) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ ID + '> (select max (' + @ ID + ') from ('+ @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strSortType +') AS TBMaxID) '+ ''+ @ strCondition + 'ORDER BY' + @ fldSort +'' + @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType end ------ return query result ----- exec sp_executesql @ strTmp -- print @ strTmp SET NOCOUNT OFF GO

How can I test it in a database?

Declare @ pageCount int declare @ Counts int exec [dbo]. [proc_ListPageInt] 'sysobjects', '*', 20, 1, @ pageCount output, @ Counts output, 'id', 0, '', 'id ', 0 print @ pageCount -- this option is optional; print @ Counts -- this option is optional.

The execution result is as follows:

Paging storage process 2:

USE [JianKunKingTestDatabase001] GO/****** Object: StoredProcedure [dbo]. [A_P_HelpPageShow] Script Date: 01/21/2015 19:19:42 ******/SET ANSI_NULLS on go set QUOTED_IDENTIFIER on go alter procedure [dbo]. [A_P_HelpPageShow] (@ tblName nvarchar (max), -- table name @ strGetFields varchar (1000) = '*', -- columns to be returned @ fldName varchar (255) = '', -- sorting field name @ PageSize int = 10, -- page size @ PageIndex int = 1, -- page number @ OrderType Bit = 0, -- set the sorting type. If the value is not 0, it is sorted in descending order @ strWhere varchar (1500) = '', -- Query condition (Note: Do not add where) @ Counts int = 0 output -- number of queried records) AS declare @ strSQL nvarchar (4000) -- subject sentence declare @ strTmp nvarchar (110) -- temporary variable declare @ strOrder nvarchar (400) -- sorting type declare @ totalRecord int -- number of queried records declare @ SqlCounts nvarchar (max) ---- construct an SQL statement for the total number of queries -- calculate the total number of Records begin if @ strWhere! = ''Set @ SqlCounts = 'select @ totalRecord = count (*) from '+ @ tblName + 'where' + @ strWhereelse set @ SqlCounts = 'select @ totalRecord = count (*) from '+ @ tblName + ''end -- print @ strWhere -- print @ SqlCounts exec sp_executesql @ SqlCounts, n' @ totalRecord int output ', @ totalRecord OUTPUT -- calculate the total number of records set @ Counts = @ totalRecord begin if @ OrderType! = 0 begin set @ strTmp = '<(select min' set @ strOrder = 'ORDER BY' + @ fldName + 'desc' -- If @ OrderType is not 0, execute descending order, this sentence is very important! End else begin set @ strTmp = '> (select max 'set @ strOrder = 'ORDER BY' + @ fldName + 'asc' end -- print @ strOrder if @ PageIndex = 1 begin if @ strWhere! = ''Set @ strSQL = 'select top' + str (@ PageSize) + ''+ @ strGetFields + 'from' + @ tblName + 'where' + @ strWhere +'' + @ strOrder else set @ strSQL = 'select top' + str (@ PageSize) + ''+ @ strGetFields + 'from' + @ tblName +'' + @ strOrder -- execute the above Code on the first page, this will speed up the execution of end else begin -- the following code gives @ strSQL the SQL code set @ strSQL = 'select top '+ str (@ PageSize) to be truly executed) + ''+ @ strGetFields + 'from' + @ tblName + 'where '+ @ FldName + ''+ @ strTmp +' ('+ @ fldName +') from (select top '+ str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName +'' + @ strOrder + ') as tblTmp)' + @ strOrder -- print @ strSQL if @ strWhere! = ''Set @ strSQL = 'select top' + str (@ PageSize) + ''+ @ strGetFields + 'from' + @ tblName + 'where' + @ fldName +'' + @ strTmp + '(' + @ fldName + ') from (select top '+ str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ strWhere +'' + @ strOrder + ') as tblTmp) and '+ @ strWhere + ''+ @ strOrder end -- print @ strSQL exec sp_executesql @ strSQL GO

How can I test it in a database?

Select count (*) from sysobjects; declare @ CountsAA int exec [dbo]. [A_P_HelpPageShow] 'sysobjects', '*', 'id', 20, '', @ CountsAA output print @ CountsAA -- This dispensable

The execution result is as follows: