Two general paging stored procedures for SQL Server 2000

The first one supports a unique primary key, and the second one supports multiple primary keys, which have been tested and are efficient.

Create proc p_viewpage
/*
No_miss paging Stored Procedure 2007.2.20 QQ: 34813284
Applicable to tables or views with a single primary key or with a unique value column
*/

@ Tablename varchar (200), -- table name
@ Fieldlist varchar (2000), -- display column name
@ Primarykey varchar (100), -- single primary key or unique value Key
@ Where varchar (1000), -- the query condition does not contain the 'where' character
@ Order varchar (1000), -- sorting does not contain the 'ORDER BY' character, such as id asc, userid DESC, and takes effect when @ sorttype = 3
@ Sorttype int, -- sorting Rule 1: Forward ASC 2: reverse DESC 3: Multi-column sorting
@ Recordercount int, -- total number of records 0: the total number of records is returned.
@ Pagesize int, -- number of records output per page
@ Pageindex int, -- current page number
@ Totalcount int output, -- total number of returned records
@ Totalpagecount int output -- total number of returned pages
As
Set nocount on
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
Return
End

Declare @ new_where1 varchar (1000)
Declare @ new_where2 varchar (1000)
Declare @ new_order 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 Set @ new_order = 'ORDER BY' + @ primarykey + 'asc'
If @ sorttype = 2 set @ new_order = 'ORDER BY' + @ primarykey + 'desc'
End
Else
Begin
Set @ new_order = 'ORDER BY' + @ order
End

Set @ sqlcount = 'select @ totalcount = count (*), @ totalpagecount = ceiling (count (*) + 0.0 )/'
+ Cast (@ pagesize as varchar) + ') from' + @ tablename + @ new_where1

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
Begin
Set @ SQL = 'select top '+ STR (@ pagesize) + ''+ @ fieldlist + 'from'
+ @ Tablename + @ new_where1 + @ new_order
End
Else
Begin
If @ sorttype = 1
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_order + ') as TMP)' + @ new_order
End
If @ sorttype = 2
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_order + ') as TMP)' + @ new_order
End
If @ sorttype = 3
Begin
If charindex (',', @ order) = 0 begin return end
Set @ SQL = 'select top '+ STR (@ pagesize) + ''+ @ fieldlist + 'from'
+ @ Tablename + @ new_where2 + @ primarykey + 'not in (select top'
+ STR (@ pagesize * (@ PageIndex-1) + ''+ @ primarykey
+ 'From' + @ tablename + @ new_where1 + @ new_order + ')'
+ @ New_order
End
End
Exec (@ SQL)
Go

 

Create proc p_public_viewpage_per
/*
No_miss General paging Stored Procedure 2007.3.1 QQ: 34813284
It is applicable to the Union primary key/single primary key/existence of multiple columns that can determine a unique row or a unique row (separated by English letters)
Call:
When querying the first page, the total record and total page number and the first record are returned:
Execute p_public_viewpage_per 'tablename', 'col1, col2, col3, col4', 'pk1, PK2, pk3 ',
'Col5> 0 and col7 <9', 'pk1 ASC, PK2 ASC, pk3 ASC ', 0, 10, 1,
@ Totalcount output, @ totalpagecount output
Other page calls, such as page 89th (assuming the total number of records returned for the first page query is 2000000 ):
Execute p_public_viewpage_per 'tablename', 'col1, col2, col3, col4', 'pk1, PK2, pk3 ',
'Col5> 0 and col7 <9', 'pk1 ASC, PK2 ASC, pk3 ASC ', 2000000,10, 89,
@ Totalcount output, @ totalpagecount output
*/

@ Tablename varchar (200), -- table name
@ Fieldlist varchar (2000), -- display column name
@ Primarykey varchar (100), -- single primary key, unique value key, or union Primary Key List (separated by English), or multiple columns that can determine a unique row (separated by English)
@ Where varchar (1000), -- the query condition does not contain the 'where' character
@ Order varchar (1000), -- sorting does not contain the 'ORDER BY' character, separated by English
@ Recordercount int, -- total number of records 0: the total number of records is returned.
@ Pagesize int, -- number of records output per page
@ Pageindex int, -- current page number
@ Totalcount int output, -- total number of returned records
@ Totalpagecount int output -- total number of returned pages
As

set nocount on
set @ fieldlist = Replace (@ fieldlist ,'','')
If @ fieldlist = '*'
begin set @ fieldlist = 'a. * 'end
else
begin
set @ fieldlist = 'a. '+ Replace (@ fieldlist,',. ')
end
while charindex (', ', @ order)> 0
begin
set @ order = Replace (@ order, ',')
end

If isnull (@ tablename, '') ='' or isnull (@ primarykey ,'') = ''
or @ recordercount <0 or @ pagesize <0 or @ pageindex <0
begin
return
end
declare @ new_where1 varchar (1000)
declare @ brief varchar (1000)
declare @ new_where3 varchar (1000)
declare @ new_where4 varchar (1000)
declare @ new_order1 varchar (1000)
declare @ new_order2 varchar (1000)
declare @ fields varchar (1000)
declare @ SQL varchar (8000)
declare @ sqlcount nvarchar (4000)

Set @ fields = @ primarykey + ','
Set @ new_where2 =''
Set @ new_where4 =''

If isnull (@ where, '') =''
Begin
Set @ new_where1 =''
Set @ new_where3 = 'where'
End
Else
Begin
Set @ new_where1 = 'where' + @ where +''
Set @ new_where3 = 'where 1 = 1'
+ Replace ('and' + @ where, 'and', 'and a. ') + 'and'
End

While charindex (',', @ fields)> 0
Begin
Set @ new_where2 = @ new_where2
+ 'A. '+ ltrim (left (@ fields, charindex (', ', @ fields)-1 ))
+ '= B.' + ltrim (left (@ fields, charindex (',', @ fields)-1) + 'and'
Set @ new_where4 = @ new_where4
+ 'B.' + ltrim (left (@ fields, charindex (',', @ fields)-1) + 'is null and'
Set @ fields = substring (@ fields, charindex (',', @ fields) + 1, Len (@ fields ))
End
Set @ new_where2 = left (@ new_where2, Len (@ new_where2)-4)
Set @ new_where4 = left (@ new_where4, Len (@ new_where4)-4)

If isnull (@ order, '') =''
Begin
Set @ new_order1 =''
Set @ new_order2 =''
End
Else
Begin
Set @ new_order1 = 'ORDER BY' + @ order
Set @ new_order2 = 'ORDER'
+ Right (replace (',' + @ order, ', .'),
Len (replace (',' + @ order, ', A.')-1)
End

Set @ sqlcount = 'select @ totalcount = count (*), @ totalpagecount = ceiling (count (*) + 0.0 )/'
+ Cast (@ pagesize as varchar) + ') from' + @ tablename
+ 'A' + @ new_where1

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
Begin
Set @ SQL = 'select top '+ STR (@ pagesize) + ''+ @ fieldlist + 'from'
+ @ Tablename + 'A' + @ new_where1 + @ new_order1
End
Else
Begin
Set @ SQL = 'select top '+ STR (@ pagesize) + ''+ @ fieldlist + 'from'
+ @ Tablename + 'a left join (select top'
+ STR (@ pagesize * (@ PageIndex-1 ))
+ ''+ @ Primarykey + 'from' + @ tablename + @ new_where1
+ @ New_order1 + ') B on' + @ new_where2 + @ new_where3
+ @ New_where4 + @ new_order2
End

Exec (@ SQL)
Go