SQL Server Paging stored procedures

Paging Stored Procedure one:

--/*-----Stored procedure Sun Wei 2005-03-28 created-------*/--/*-----The data is processed 2 to make the first half of the query the same data as the second half of the query-------*/--/*-----Stored Procedure paging Sun Wei 2 005-04-21 Modify Add distinct query function-------*/--/*-----Stored Procedure paging processing Sun Wei 2005-05-18 Modifying a multi-field collation problem-------*/--/*-----Stored Procedure paging Sun Wei 2005-06-15 Modify the multi-field sort modification-------*/--/*-----Stored Procedure paging processing Sun Wei 2005-12-13 Modify modify data paging has greatly improved performance for top Max mode-------*/--/*----- Disadvantage: The previous not in version primary key can only be an integer field, such as the primary key is a GUID type use the version of not in mode-------*/CREATE PROCEDURE dbo.proc_listpageint (@tblName nvarchar ( ),----the connection to the table or tables to be displayed @fldName nvarchar = ' * ',----the list of fields to display @pageSize int = Ten,----the number of records displayed per page @page int = 1,----to display The record for that page @pageCount int = 1 OUTPUT,----the total number of pages after the query results @Counts int = 1 output,----of records queried @fldSort nvarchar = null,----row Order field List or condition @Sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if multi-field arrangement sort refers to the order of the last sort field (the last sort field is not sorted)-Program passes such as: ' Sorta ASC,SORTB Desc, SORTC ') @strCondition nvarchar = NULL,----the query condition without the where @ID nvarchar,----The primary key @Dist bit = 0----Whether to add the DI of the query field Stinct default 0 Not Add/1 Add) as SET NOCOUNT on Declare @sqlTmp NVArchar (----) store dynamically generated SQL statements Declare @strTmp nvarchar----The query statement that holds the total number of query results Declare @strID nvarchar (+)---- The query statement that holds the start or end ID of the query Declare @strSortType nvarchar----data collation a Declare @strFSortType nvarchar----data collation B Declare @SqlSelect nvarchar----SQL constructs a query with distinct Declare @SqlCounts nvarchar----SQL constructs an if for the total number of queries that contain distinct @Dist = 0 Begin Set @SqlSelect = ' SELECT ' Set @SqlCounts = ' Count (*) ' End ELSE begin set @SqlSelect = ' SELECT distinct ' Set @S qlcounts = ' Count (DISTINCT ' [email protected]+ ') ' End If @Sort =0 begin set @strFSortType = ' ASC ' Set @strSortType = ' D The ESC ' end ELSE begin set @strFSortType = ' DESC ' Set @strSortType = ' ASC ' end--------generate a query statement----------here @strtmp to get the number of query results  If @strCondition is null or @strCondition = "--no display condition is set to begin set @sqlTmp = @fldName + ' from ' + @tblName set @strTmp = @SqlSelect + ' @Counts = ' [email protected]+ ' from ' [email protected]set @strID = ' from ' + @tblName end else Begi N Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition Set @strTmp = @SqlSelect + ' @Counts = ' [email protected]+ ' from ' [email  protected] + ' WHERE (1>0) ' + @strCondition set @strID = ' from ' + @tblName + ' WHERE (1>0) ' + @strConditio N End----Get 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--Gets the total number of pages set @pageCount = (@[email protected])/@pageSize/**//** The current page is greater than total Page **/if @page > @pageCount set @[email protected]--/*-----Data Paging 2-------*/DECLARE @pageIndex INT--Total/ Page size declare @lastcount INT--Total% page size Set @pageIndex = @tmpCounts/@pageSize set @lastcount = @tmpCounts% @pageSize if @lastco Unt > 0 Set @pageIndex = @pageIndex + 1 Else Set @lastcount = @pagesize--//*** show pagination if @strCondition is null or @strCon Dition= '--no set display condition begin if @pageIndex <2 or @page <[email protected]/2 + @pageIndex% 2--first half data processing begin IF @page =1 Set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' [email protected]+ ' ORDER BY ' + @fldSort + ' + @ Strfsorttype else begin set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' [EMAIL&NB sp;protected]+ ' where ' [email protected]+ ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@p AGE-1) as Varchar + ' + @ID + ' from ' [email protected]+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid)  ' + ' ORDER BY ' + @fldSort + ' + @strFSortType end end ELSE begin set @page = @[email protected]+1--the latter part data processing if @page <= 1--The last page of data shows the set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@lastcount as VARCHAR (4)) + ' + @fldName + ' from ' [email protected]+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') as TEMPTB ' + ' ORDER by ' + @fldSor T + ' + @strFSortType else set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@pageSize as Varcha R (4)) + ' + @fldName + ' frOm ' [email protected]+ ' where ' [email protected]+ ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@ Pagesize* (@page-2) [email protected] as Varchar) + ' + @ID + ' from ' [email protected]+ ' ORDER by ' + @fldSort + ' + @strSortType + ') as Tbmaxid) ' + ' ORDER BY ' + @fldSort + "+ @strSortType + ') as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @ Strfsorttype end END ELSE--query condition begin if @pageIndex <2 or @page <[email protected]/2 + @pageIndex% 2--first half According to the processing begin if @page =1 set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' [email  protected]+ ' where 1=1 ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType else begin set @[email protecte d]+ ' top ' + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' [email protected]+ ' where ' [email protected]+ ' & lt; (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + "+ @ID + ' from ' [email& Nbsp;protected]+ ' where(1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + "+ @strFSortType + ') as Tbminid) ' + ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType end end ELSE begin set @page = @[email protected]+1--Data processing if @page <= 1--The last page of data shows set @[ Email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@lastcount as VARCHAR (4) + "+ @fldName + ' from ' [EMA Il protected]+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + "+ @strSortType + ') as TEMPTB ' + ' ORDER by ' + @ Fldsort + ' + @strFSortType else set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' [email protected]+ ' where ' [email protected]+ ' > (select max (' + @ID + ') fro M (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) [email protected] as Varchar) + ' + @ID + ' from ' [Email pro Tected]+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid) ' + ' + @strCondition + ' or Der By ' + @fldSort+ ' + @strSortType + ') as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType end end------Return query Results-----EXEC sp_executesql @  Strtmp--print @strTmp SET NOCOUNT OFF GO

How to test in the database?

The following results are performed:

Paging stored Procedure two:

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),--The table name @strGetFields varchar (1000) = ' * ',--the column that needs to be returned @fldName varchar (255) = ',--the field name of the sort @PageSize int = 10,--page dimension @PageIndex int = 1,--page number @OrderType bit = 0,--set sort type, non 0 value descending @strWhere varchar (1500) = ',--query condition (Note: Do not add where) @Counts int = 0 OUTPUT--the number of records queried) as declare @strSQL nvarchar (4000)--The subject sentence declare @strTmp nvarchar (110) --Temporary variable declare @strOrder nvarchar (400)--sort type declare @totalRecord INT--Number of records queried declare @SqlCounts nvarchar (max)----to total Number of queries for SQL constructs-calculates the total number of records begin if @strWhere! = ' Set @SqlCounts = ' Select @totalRecord =count (*) from ' + @tblName + ' where ' [E Mail protected]else Set @SqlCounts = ' Select @totalRecord =count (*) from ' + @tblName + ' End--print @strWhere--pri NT @SqlCounts exec sp_executesql @SqlCounts,N ' @totalRecord int OUTPUT ', @totalRecord output--calculates the total number of records set @[email protected] BEGIN if @OrderType! = 0 Begin Set @str TMP = ' < (select min ' Set @strOrder = ' ORDER BY ' + @fldName + ' desc '--if @ordertype is not 0, it is important to perform descending order! End ELSE begin set @strTmp = ' > (select Max ' Set @strOrder = ' ORDER BY ' + @fldName + ' ASC ' end--print @strOrder if @P Ageindex = 1 BEGIN if @strWhere! = ' Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from ' + @tblName + ' where ' + @strWhere + ' + @strOrder else set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email prote cted]+ ' from ' + @tblName + ' + @strOrder--executes the above code on the first page, which speeds up the execution speed end else Begin--The following code gives @strsql the SQL code set to actually execute @strSQ L = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from ' + @tblName + ' where ' + @fldName + ' + @strTmp + ' (' + @fldName + ') from (select top ' + str (@PageIndex-1) * @PageSize) + "+ @fldName + ' from ' + @tblName + ' + @str Order + ') as tbltmp) ' + @strOrder--print@strSQL if @strWhere! = ' Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from ' + @tblName + ' WHERE ' + @fldName + ' + ' + @strTmp + ' (' + @fldName + ') from (select top ' + str ((@PageIndex-1) * @PageSize) + ' + @fld Name + ' from ' + @tblName + ' where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder E  nd end--print @strSQL exec sp_executesql @strSQL GO

How to test in the database?

Select COUNT (*) from sysobjects;d eclare @CountsAA int exec [dbo]. [A_p_helppageshow] ' sysobjects ', ' * ', ' id ', ', ', ', ', @CountsAA output print @CountsAA--this dispensable

The results of the implementation are as follows:

SQL Server Paging stored procedures