SQL Server Tens of millions of page-level paging stored procedures

With the development of information system, more and more informations are used, and more and more and more and more and more and more and more and more, when the quantity is more and more, this is more and more important in the data display, especially the report, the display of the Tens data quantity must be shown by pagination.


If object_id (' sp_pagination ') is not null


drop proc Sp_pagination


Go


Create PROCEDURE sp_pagination


/**//*


***************************************************************


* * million order paging stored procedures * *


***************************************************************


parameter Description:


. Tables: Table name, view (trying to get a little problem right here)


. PrimaryKey: Primary keyword


. Sort: sorted statements without ORDER by example: NewsID desc,orderrows Asc


. CurrentPage: Current page number


. PageSize: Paging Size


. Filter: Filtering statements without where


. Group:group statement without GROUP by


***************************************************************/


(


@Tables varchar (2000),


@PrimaryKey varchar (500),


@Sort varchar = NULL,


@CurrentPage int = 1,


@PageSize int = 5,


@Fields varchar (2000) = ' * ',


@Filter varchar (1000) = NULL,


@Group varchar (1000) = NULL


)


as


/**//* Default sort */


IF @Sort is NULL OR @Sort = '


SET @Sort = @PrimaryKey


DECLARE @SortTable varchar (1000)


DECLARE @SortName varchar (1000)


DECLARE @strSortColumn varchar (1000)


DECLARE @operator char (2)


DECLARE @type varchar (1000)


DECLARE @prec int


/**//* set the sort statement. * *


IF CHARINDEX (' DESC ', @Sort) >0


BEGIN


SET @strSortColumn = REPLACE (@Sort, ' DESC ', ')


SET @operator = ' <= '


End


ELSE


BEGIN


IF CHARINDEX (' ASC ', @Sort) = 0


print ' 1 '


Print REPLACE (@Sort, ' ASC ', ')


SET @strSortColumn = REPLACE (@Sort, ' ASC ', ')


Print @strSortColumn


SET @operator = ' >= '


Print @operator


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


Print @SortTable


Print @SortName


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


--print @type


--print @prec


IF CHARINDEX (' char ', @type) > 0


SET @type = @type + ' (' + CAST (@prec as varchar) + ') '


DECLARE @strPageSize varchar (500)


DECLARE @strStartRow varchar (500)


DECLARE @strFilter varchar (1000)


DECLARE @strSimpleFilter varchar (1000)


DECLARE @strGroup varchar (1000)


/**//* Default Current page */


IF @CurrentPage < 1


SET @CurrentPage = 1


/**//* Set Paging parameters. * *


SET @strPageSize = CAST (@PageSize as varchar (500))


SET @strStartRow = CAST ((@CurrentPage-1) * @PageSize + 1) as varchar (500))


/**//* filtering and Grouping statements. */


IF @Filter is not NULL and @Filter!= '


BEGIN


SET @strFilter = ' WHERE ' + @Filter + '


SET @strSimpleFilter = ' and ' + @Filter + '


End


ELSE


BEGIN


SET @strSimpleFilter = '


SET @strFilter = '


End


IF @Group is not NULL and @Group!= '


SET @strGroup = ' GROUP by ' + @Group + '


ELSE


SET @strGroup = '


/*print @type


Print @strStartRow


Print @strSortColumn


Print @Tables


Print @strFilter


Print @strGroup


Print @Sort */


/**//* Execute Query statement * *


EXEC (


'


DECLARE @SortColumn ' + @type + '


SET RowCount ' + @strStartRow + '


SELECT @SortColumn = ' + @strSortColumn + ' from ' + @Tables + @strFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '


SET RowCount ' + @strPageSize + '


SELECT ' + @Fields + ' from ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '


'


)

Below is an instance of how to invoke this stored procedure in the Database Query Analyzer. As shown in the figure, the result set of the query traffic_sites the original table. Executes the paging stored procedure script as an example of a traffic_sites table: exec sp_pagination ' traffic_sites ', ' id ', ' ID ASC ', 1, 5, ' * ', ', ' Execute the above SQL results as shown in the figure: