Several paging stored procedures

Method 1:

Create procedure sp_page
@ TB varchar (50), -- table name
@ Col varchar (50), -- pagination by this column
@ Coltype int, -- @ Col column type, 0-number type, 1-character type, 2-Date and Time Type
@ Orderby bit, -- sort, 0-order, 1-inverted
@ Collist varchar (800), -- List of fields to be queried, * indicates all fields
@ Pagesize int, -- number of records per page
@ Page int, -- specify the page
@ Condition varchar (800), -- Query Condition
@ Pages int output -- total number of pages
As
Declare @ SQL nvarchar (4000), @ where1 varchar (800), @ where2 varchar (800)
If @ condition is null or rtrim (@ condition) =''
Begin -- no query Conditions
Set @ where1 = 'where'
Set @ where2 =''
End
Else
Begin -- with query Conditions
Set @ where1 = 'where ('+ @ condition +') and '-- this condition is added if conditions exist.
Set @ where2 = 'where ('+ @ condition +') '-- this condition is added if no conditions exist.
End
Set @ SQL = 'select @ pages = ceiling (count (*) + 0.0)/'+ Cast (@ pagesize as varchar) +
') From' + @ TB + @ where2
Exec sp_executesql @ SQL, n' @ pages int output', @ pages output -- calculate the total number of pages
If @ orderby = 0
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
'From' + @ TB + @ where1 + @ Col + '> (select max (' + @ Col + ')' +
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + ') T) Order by' + @ col
Else
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
'From' + @ TB + @ where1 + @ Col + '<(select Min (' + @ Col + ')' +
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + 'desc) T) Order by' +
@ Col + 'desc'
If @ page = 1 -- first page
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist + 'from' + @ TB +
@ Where2 + 'ORDER BY' + @ Col + case @ orderby when 0 then' 'else' DESC 'end
Exec (@ SQL)
Go

--------------------------------------------------
Method 2:

Create procedure page_list_test
@ Tbname varchar (50), -- table name
@ Pagesize int, -- size of each page
@ Pageindex int, -- page index, starting from 1
@ Docount bit, -- whether to obtain the number of records; 1 is; 0 is not
@ Name varchar (50), -- field value
@ Clound varchar (50) -- field name
As
Set nocount on
Declare @ SQL nvarchar (4000)

If (@ docount = 1)
Set @ SQL = 'select count (*) from '+ @ tbname
+ 'Where' + @ Clound + '=' + ''' + @ name + ''''
Else
Begin
If @ pageindex = 1
Set @ SQL = 'select top '+ convert (varchar, @ pagesize) +' * from' + @ tbname
+ 'Where' + ''+ @ Clound + '=' + ''' + @ name + ''''
+ 'Order by id desc'
Else
Begin
Set @ SQL = 'select top '+ convert (varchar, @ pagesize) +' * from' + @ tbname
+ 'Where' + ''+ @ Clound + '=' + ''' + @ name + ''''
+ 'And ID <(select Min (ID) from (select top' + convert (varchar, (@ pageindex-1) * @ pagesize) + 'id from' + @ tbname
+ 'Where' + ''+ @ Clound + '=' + ''' + @ name + ''''
+ 'Order by id desc) T )'
+ 'Order by id desc'
End
End

Exec (@ SQL)
Set nocount off
Go

---------------------------------------------------------
Method 3:

Alter procedure DBO. getpagingrecord
(
@ Tablename varchar (100), -- table name or view chart
@ Fieldlist varchar (4000) = '*', -- List of fields to be selected
@ Orderfield varchar (100), -- Sort Field
@ Keyfield varchar (100), -- primary key
@ Pageindex int, -- page number, starting from 0
@ Pagesize Int = 20, -- page size
@ Strwhere varchar (4000), -- Condition
@ Ordertype bit = 1 -- sort, 1, descending, 0, ascending

)
As
/**//*
Name: getpagingrecord
Purpose: Sort pages by any field
Author: bodhi tree (Mark Ma)
Time: 2004-12-14
Statement: ThisCodeYou can use and reprint it for free.
*/
Set nocount on

Declare @ sqlstr varchar (6000)
-- Process dangerous characters in SQL and process conditions into easy-to-embed forms
Set @ strwhere = Replace (@ strwhere ,'''','''''')
Set @ strwhere = Replace (@ strwhere ,'--','')
Set @ strwhere = Replace (@ strwhere ,';','')
Set @ sqlstr = 'Clare @ curpagenum int ;'
Set @ sqlstr = @ sqlstr + 'Clare @ nextpagenum int ;'
Set @ sqlstr = @ sqlstr + 'set @ curpagenum = '+ Cast (@ pageindex as varchar) +' * '+ Cast (@ pagesize as varchar) + ';'
Set @ sqlstr = @ sqlstr + 'set @ nextpagenum = '+ Cast (@ pageindex + 1 as varchar) +' * '+ Cast (@ pagesize as varchar) + ';'
Set @ sqlstr = @ sqlstr + 'Clare @ sqlstr varchar (6000 );'
If @ ordertype = 1
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = 'select' + @ fieldlist + 'from (select top ''+ Cast (@ nextpagenum as varchar) + ''' * from

'+ @ Tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'desc) as a where' + @ keyfield +' not in (

Select top ''+ Cast (@ curpagenum as varchar) + ''' + @ keyfield + 'from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield + 'desc') order by' + @ orderfield + 'desc '';'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = 'select' + @ fieldlist + 'from (select top ''+ Cast (@ nextpagenum as varchar) + ''' * from

'+ @ Tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'asc) as a where' + @ keyfield +' not in (

Select top ''+ Cast (@ curpagenum as varchar) + ''' + @ keyfield + 'from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield + 'asc) order by' + @ orderfield + 'asc '';'
End
Set @ sqlstr = @ sqlstr + 'execute (@ sqlstr )'
Print @ sqlstr
Execute (@ sqlstr)

// Ensure ease of use.

///*************************///
Over the past two years, I have gradually become accustomed to talking and not practicing.
///*************************///

Bytes --------------------------------------------------------------------------------------------------
Method 4:
Create procedure quest
@ Class int, -- type of the problem
@ Pagesize int, -- number of information displayed on each page
@ Currentpage int, -- current page number
@ Totalpage int output -- total number of pages
As
Declare @ strquery as varchar (1000) -- query statement
Declare @ rowcount as int -- total number of rows
-- Query the total number of records based on the condition to display the information of the paging function.
Select @ rowcount = count (ID) from (select ID from question where type = @ class and put_state = 1 and del = 0) as temphelp
Set @ totalpage = ceiling (cast (@ rowcount as float)/cast (@ pagesize as float) -- obtains the total number of pages of a record based on the total number of entries.
If @ currentpage> 1 -- determines if the current page is greater than 1, execute the next step.
Begin
-- If the current page is greater than the total number of pages, set the current page to the total number of pages.
If @ currentpage> @ totalpage
Begin
Set @ currentpage = @ totalpage
End
-- Execute SQL query to retrieve the record to be queried
Set @ strquery = 'select top' + Cast (@ pagesize as varchar (10) + 'id, title, contactman, phone, email, type, content, answer_note, ORD, put_state, add_date, answer_date, answer_date, handler from (select ID, title, contactman, phone, email, type, content, answer_note, ORD, put_state, add_date, answer_date, handler from question where type = '+ Cast (@ Class As varchar (10) +' and put_state = 1 and del = 0) as temphelp where id not in (select top '+ Cast (@ currentPage-1) * @ pagesize as varchar (10) + 'id from (select ID, add_date, answer_date from question where type = '+ Cast (@ Class As varchar (10) +' and put_state = 1 and del = 0) as temphelp order by (Case ord when 1 then 0 else 1 end), add_date DESC) order by (Case ord when 1 then 0 else 1 end), add_date DESC'
End
Else -- if the current page is not greater than 1, the query will be executed directly and the query will be skipped to determine the record to be obtained.
Begin
Set @ strquery = 'select top' + Cast (@ pagesize as varchar (10) + 'id, title, contactman, phone, email, type, content, answer_note, ORD, put_state, add_date, answer_date, answer_date, handler from (select ID, title, contactman, phone, email, type, content, answer_note, ORD, put_state, add_date, answer_date, handler from question where type = '+ Cast (@ Class As varchar (10) +' and put_state = 1 and del = 0) as temphelp order by (Case ord when 1 then 0 else 1 end), add_date DESC'
End
Exec (@ strquery) -- execute the statement
Return
Go

 

 

----------------------------------------------------
Method 5:
Create proc sp_publicturnpage (
@ Tbname nvarchar (2000) = '', -- table name, such as pinyin
@ Pagesizeint = 10, -- number of records on each page. The default value is 10.
@ Curpageint = 1, -- indicates the current page 1
@ Keyfieldnvarchar (100) = 'id', -- specifies the key field name. The default value is ID. This field must be an index in a table or a field without duplicates or null.
@ Keyascdescnvarchar (4) = 'asc ', -- ascending or descending of keywords. The default value is ascending ASC and descending is desc.
@ Fieldsnvarchar (2000) = '*', -- specifies the selected column name. All columns are selected by default.
@ Conditionnvarchar (2000) = '', -- Where condition, empty by default
@ Ordernvarchar (200) = ''-- sorting condition. The default value is null.
)
If @ tbname =''
Begin
Raiserror ('Please specify the table name! ', 11,1)
Return
End
If @ pagesize <= 0 or @ curpage <0
Begin
Raiserror ('the number of current pages and records on each page must be greater than zero! ', 11,1)
Return
End
If @ keyascdesc = 'desc'
Set @ keyascdesc = '<'
Else
Set @ keyascdesc = '>'
If @ condition <>''
Set @ condition = 'where' + @ Condition
Declare @ SQL nvarchar (2000)

Set @ SQL =''
If @ curpage = 1
Set @ SQL = @ SQL + 'select top' + Cast (@ pagesize as nvarchar (20 )) + ''+ @ fields + 'from' + @ tbname + @ condition +'' + @ order
Else
Begin
Declare @ itopnum int
Set @ itopnum = @ pagesize * (@ curpage-1)
Set @ SQL = @ SQL + 'Clare @ slastvalue nvarchar (100) '+ char (13)
Set @ SQL = @ SQL + 'select top' + Cast (@ itopnum as nvarchar (20 )) + '@ slastvalue =' + @ keyfield + 'from' + @ tbname + @ condition + ''+ @ order + char (13)

Declare @ condition2 nvarchar (200)
If @ condition =''
Set @ condition2 = 'where' + @ keyfield + @ keyascdesc + '@ slastvalue'
Else
Set @ condition2 = 'and' + @ keyfield + @ keyascdesc + '@ slastvalue'
Set @ SQL = @ SQL + 'select top' + Cast (@ pagesize as nvarchar (20 )) + ''+ @ fields + 'from' + @ tbname + @ condition + @ condition2 + @ order
End
Execute sp_executesql @ SQL

-----------------------------------------------------------------
Method 6:
Create proc page
@ Recordcountint output,
@ Querystr nvarchar (100) = 'table1', -- table name, view name, and query statement
@ Pagesize Int = 20, -- size of each page (number of rows)
@ Pagecurrent Int = 2, -- the page to be displayed starts from 0
@ Fdshow nvarchar (1000) = '*', -- List of fields to be displayed
@ Identitystr nvarchar (100) = 'id', -- primary key
@ Wherestr nvarchar (200) = '1 = 1 ',
@ Fdorder nvarchar (100) = 'desc' -- only DESC or ASC can be used for sorting.
As
-- By quxh 2005.7.19
Declare
@ Sqlnvarchar (2000)

Set @ wherestr = Replace (@ wherestr ,';','')
Set @ wherestr = Replace (upper (@ wherestr), 'delete ','')
Set @ wherestr = Replace (@ wherestr, 'drop ','')
Set @ wherestr = Replace (@ wherestr, 'update ','')
Set @ wherestr = Replace (@ wherestr, 'from ','')
Set @ wherestr = Replace (@ wherestr ,'--','')
Set @ wherestr = Replace (@ wherestr, 'execute ','')

If @ wherestr = ''begin
Set @ wherestr = '1 = 1'
End

If @ pagecurrent = 0 begin
set @ SQL = 'select top' + Cast (@ pagesize as nvarchar (3 )) + ''+ @ fdshow + 'from' + @ querystr + 'where' + @ wherestr + 'ORDER BY' + @ identitystr +'' + @ fdorder
end

else begin
If upper (@ fdorder) = 'desc' begin
set @ SQL = 'select top' + Cast (@ pagesize as nvarchar (3 )) + ''+ @ fdshow + 'from' + @ querystr + 'where' + @ wherestr + 'and' + @ identitystr + '<(select Min (' + @ identitystr +') from (select top '+ Cast (@ pagesize * @ pagecurrent as nvarchar (10 )) + ''+ @ identitystr + 'from' + @ querystr + 'where' + @ wherestr + 'ORDER BY' + @ identitystr + 'desc) as t) order by '+ @ identitystr + 'desc'
end
else begin
set @ SQL = 'select top' + Cast (@ pagesize as nvarchar (3)) + ''+ @ fdshow + 'from' + @ querystr + 'where' + @ wherestr + 'and' + @ identitystr + '> (select max (' + @ identitystr +') from (select top '+ Cast (@ pagesize * @ pagecurrent as nvarchar (10 )) + ''+ @ identitystr + 'from' + @ querystr + 'where' + @ wherestr + 'ORDER BY' + @ identitystr + 'asc) as t) order by '+ @ identitystr + 'asc'
end
-- print @ SQL
execute (@ SQL)

If (@ recordcount is null or @ recordcount <0) begin
Declare @ tsql nvarchar (200)
Set @ tsql = n' select @ recordcount = count (*) from '+ @ querystr + 'where' + @ wherestr
Exec sp_executesql @ tsql, n' @ recordcount int output', @ recordcount output
Select @ recordcount
End
Go

------------------------------------------------------------------

URL provided by saucer
I decided to use the rowcount method wherever possible.

Create procedure paging_rowcount
(
@ Tables varchar (1000 ),
@ PK varchar (100 ),
@ Sort varchar (200) = NULL,
@ Pagenumber Int = 1,
@ Pagesize Int = 10,
@ Fields varchar (1000) = '*',
@ Filter varchar (1000) = NULL,
@ Group varchar (1000) = NULL)
As

/* Default sorting */
If @ sort is null or @ sort =''
Set @ sort = @ PK

/* Find the @ PK type */
Declare @ sorttable varchar (100)
Declare @ sortname varchar (100)
Declare @ strsortcolumn varchar (200)
Declare @ operator char (2)
Declare @ Type varchar (100)
Declare @ prec int

/* Set sorting variables .*/
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 (50)
Declare @ strstartrow varchar (50)
Declare @ strfilter varchar (1000)
Declare @ strsimplefilter varchar (1000)
Declare @ strgroup varchar (1000)

/* Default page number */
If @ pagenumber <1
Set @ pagenumber = 1

/* Set paging variables .*/
Set @ strpagesize = cast (@ pagesize as varchar (50 ))
Set @ strstartrow = cast (@ pagenumber-1) * @ pagesize + 1) as varchar (50 ))

/* Set filter & group variables .*/
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 dynamic query */
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 +'
'
)

Go