CopyCode The Code is as follows: Set ansi_nulls on
Set quoted_identifier on
Go
Create procedure [DBO]. [sp_pagination]
/**//*
**************************************** ***********************
** Tens of millions of paging stored procedures **
**************************************** ***********************
Parameter description:
1. Tables: Table Name, View
2. primarykey: Primary Key
3. Sort: Sorting statement without order by such as newsid DESC and orderrows ASC
4. currentpage: Current page number
5. pagesize: page size
6. filter: Filter statement without where
7. Group: Group statement without group
**************************************** ***********************/
(
@ Tables varchar (2000 ),
Primarykey varchar (500 ),
@ Sort varchar (500) = NULL,
@ Currentpage Int = 1,
@ Pagesize int,
@ Fields varchar (2000) = '*',
@ Filter varchar (1000) = NULL,
@ Group varchar (1000) = NULL
)
As
/** // * Default sorting */
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 sorting statement .*/
If charindex ('desc', @ sort)> 0
Begin
Set @ strsortcolumn = Replace (@ sort, 'desc ','')
Set @ operator = '<='
End
Else
Begin
If charindex ('asc ', @ sort) = 0
Set @ strsortcolumn = Replace (@ sort, 'asc ','')
Set @ 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
End
Select @ type = T. Name, @ prec = C. prec
From sysobjects o
Join syscolumns C on O. ID = C. ID
Join policypes 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 (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 the paging parameter .*/
Set @ strpagesize = cast (@ pagesize as varchar (500 ))
Set @ strstartrow = cast (@ currentpage-1) * @ pagesize + 1) as varchar (500 ))
/** // * Filter and group statement .*/
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 =''
/** // * Execute the 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 '+ @ sort +'
'
)