MSSQL Tens paging stored procedure sharing

The code is as follows

Copy Code

Create PROC P_viewpage /**//* Nzperfect [No_miss] Efficient common paging stored procedure (bidirectional retrieval) 2007.5.7 qq:34813284 Warning: Tables or views that apply to a single primary key or existence of a unique value column Ps:sql statement is 8000 bytes, please note that the incoming parameter and the total SQL length do not exceed the specified range */ @TableName VARCHAR (200),--table name @FieldList VARCHAR (2000),--Display column names, if all fields are * @PrimaryKey VARCHAR (100),--single primary key or unique value key @Where VARCHAR (2000),--the query condition does not contain a ' Where ' character, such as Id>10 and Len (userid) >9 @Order VARCHAR (1000),--sorting does not contain an ' order by ' character, such as ID Asc,userid desc, you must specify ASC or DESC -Note that when the @sorttype=3 comes into effect, remember to add the primary key at the end, otherwise it will make you more depressed @SortType INT,--collation 1: Positive order ASC 2: Reverse DESC 3: Multi-column Sorting method @RecorderCount INT,--Total records 0: Returns the total record @PageSize INT--number of records output per page @PageIndex INT,--current page number @TotalCount INT OUTPUT,--Returns the total record @TotalPageCount INT OUTPUT--Total number of pages returned 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 (@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 (replace (@new_order2, ' ASC, ', ' {ASC}, '), ' DESC, ', ' {DESC}, ') SET @new_order2 = replace (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 (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 primary key positive order ordering BEGIN IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval 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-primary key reverse ordering only BEGIN IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval 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-Multiple column sort, must contain the primary key, and the last place is not processed BEGIN IF CHARINDEX (', ' + @PrimaryKey + ', ', ' + @Order) = 0 BEGIN PRINT (' err_02 ') return end IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval 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)

Wrote one of his own

The code is as follows

Copy Code

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 levels of paging stored procedures * * *************************************************************** Parameter description: 1.Tables: Table name, view 2.PrimaryKey: Primary keyword 3.Sort: Sort statements without ORDER by example: NewsID desc,orderrows ASC 4.CurrentPage: Current page number 5.PageSize: Paging Size 6.Filter: Filter statements, without where 7.group:group statement without Group by ***************************************************************/ ( @Tables varchar (1000), @PrimaryKey varchar (100), @Sort varchar = NULL, @CurrentPage int = 1, @PageSize int = 10, @Fields varchar (1000) = ' * ', @Filter varchar (1000) = NULL, @Group varchar (1000) = NULL ) As /* Default sort */ 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 sort 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 systypes 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 statements. * * 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 Query statement * * DECLARE @STRORDER varchar (50) If CHARINDEX (', ', @strSort) >0 Set @STRORDER =substring (@strSort, 0, CHARINDEX (', ', @strSort)) Else Set @STRORDER = @strSort EXEC ( ' DECLARE @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 ' + @st rsimplefilter+ ') t ' + ' Where t.rownumber between ' + @strStartRow + ' and @strENDCOUNT ' + @strGroup + @strSort + ') Go