Stored Procedure of SQL Server paging (available for test and available for sorting pages of non-dimension One-value fields)

It takes nine seconds to query a table whose structure is written down in 0.3 million records.

Id int No 4
Memberid int No 4
Sectype int No 4
Sectitle varchar No 100
Sectitlecolor varchar No 20
Secmap varchar No 50
Areaid varchar No 50
Roadid int No 4
District varchar No 50
Realtyrate varchar No 50
Isparking bit No 1
Partrate varchar No 50
Floors int No 4
Totalfloors int No 4
Housetype int No 4
Housearea numeric No 9
Usearea int No 4
Rooms int No 4
Hils int No 4
Kitchens int No 4
Wei int No 4
Yangtai int No 4
Ownership varchar No 50
Price numeric No 9
Unit int No 4
Secdegree datetime No 8
Matching varchar No 100
Renovation int No 4
Address nvarchar No 300
Traffic nvarchar No 300
Memberip varchar No 30
Duration datetime No 8
Looktime varchar No 50
Tags varchar No 100
Remarks ntext No 16
Hits int No 4
Flag bit No 1
Isrecom bit No 1
Recomdate datetime No 8
Istop bit No 1
Topdate datetime No 8
Isaction bit No 1
Createdate datetime No 8
Updatetime datetime No 8
Roomface varchar No 50
Housepic varchar no 1000
Status int No 4
Totalprice numeric No 9
Isjingping bit No 1

 

 

-- Start Process

Create proc test2_sp_pageview
@ Tbname sysname, -- Name of the table to be displayed by PAGE
@ Fieldkey nvarchar (1000), -- used to locate the record's primary key (unique key) field, which can be multiple fields separated by commas
@ Pagecurrent Int = 1, -- the page number to be displayed
@ Pagesize Int = 10, -- size of each page (number of records)
@ Fieldshow nvarchar (1000) = '', -- List of fields to be displayed separated by commas (,). If this parameter is not specified, all fields are displayed.
@ Fieldorder nvarchar (1000) = '', -- List of sorting fields separated by commas (,). You can specify DESC/ASC after a field.
@ Where nvarchar (1000) = '', -- Query Condition
@ Pagecount int output -- total number of pages
As
Set nocount on
-- Check whether the object is valid
If object_id (@ tbname) is null
Begin
Raiserror (n' object "% s" does not exist ', @ tbname)
Return
End
If objectproperty (object_id (@ tbname), N 'istable') = 0
And objectproperty (object_id (@ tbname), N 'isview') = 0
And objectproperty (object_id (@ tbname), n'istablefunction') = 0
Begin
Raiserror (n' "% s" is not a table, view, or table value function ', @ tbname)
Return
End

-- Paging field check
If isnull (@ fieldkey, n'') =''
Begin
Raiserror (n' primary key (or unique key) required for paging processing)
Return
End

-- Other parameter checks and specifications
If isnull (@ pagecurrent, 0) <1 Set @ pagecurrent = 1
If isnull (@ pagesize, 0) <1 Set @ pagesize = 10
If isnull (@ fieldshow, n') = n' set @ fieldshow = n '*'
If isnull (@ fieldorder, n'') = n''
Set @ fieldorder = n''
Else
Set @ fieldorder = n 'ORDER BY' + ltrim (@ fieldorder)
If isnull (@ where, n'') = n''
Set @ where = n''
Else
Set @ where = n' where ('+ @ where + N ')'

-- If @ pagecount is null, the total number of pages is calculated (this design can only calculate the total number of pages for the first time. When called later, the total number of pages is returned to the stored procedure to avoid re-calculation of the total number of pages, if you do not want to calculate the total number of pages, you can assign a value to @ pagecount)
If @ pagecount is null
Begin
Declare @ SQL nvarchar (4000)
Set @ SQL = n' select @ pagecount = count (*)'
+ N' from' + @ tbname
+ N'' + @ where
Exec sp_executesql @ SQL, n' @ pagecount int output', @ pagecount output
Set @ pagecount = (@ pagecount + @ PageSize-1)/@ pagesize
End

-- Calculate the topn value displayed by PAGE
Declare @ topn varchar (20), @ topn1 varchar (20)
Select @ topn = @ pagesize,
@ Topn1 = (@ PageCurrent-1) * @ pagesize

-- Display directly on the first page
If @ pagecurrent = 1
Exec (N 'select top' + @ topn
+ N' + @ fieldshow
+ N' from' + @ tbname
+ N'' + @ where
+ N'' + @ fieldorder)
Else
Begin
-- Process aliases
If @ fieldshow = n '*'
Set @ fieldshow = n' .*'

-- Generate primary key (unique key) Processing Conditions
Declare @ where1 nvarchar (4000), @ where2 nvarchar (4000 ),
@ S nvarchar (1000), @ field sysname
Select @ where1 = n'', @ where2 = n'', @ s = @ fieldkey
While charindex (n', ', @ s)> 0
Select @ field = left (@ s, charindex (n', ', @ s)-1 ),
@ S = stuff (@ s, 1, charindex (n', ', @ s), n ''),
@ Where1 = @ where1 + N' and A. '+ @ field + N' = B.' + @ field,
@ Where2 = @ where2 + N' and B. '+ @ field + N' is null ',
@ Where = Replace (@ where, @ field, n'a. '+ @ field ),
@ Fieldorder = Replace (@ fieldorder, @ field, N 'a. '+ @ field ),
@ Fieldshow = Replace (@ fieldshow, @ field, N 'a. '+ @ field)
Select @ where = Replace (@ where, @ S, N 'a. '+ @ s ),
@ Fieldorder = Replace (@ fieldorder, @ S, N 'a. '+ @ s ),
@ Fieldshow = Replace (@ fieldshow, @ S, N 'a. '+ @ s ),
@ Where1 = stuff (@ where1 + N' and A. '+ @ s + N' = B.' + @ s, n ''),
@ Where2 = case
When @ where = ''then n' where ('
Else @ where + N' and ('
End + N 'B.' + @ s + N' is null' + @ where2 + N ')'

-- Execute Query
Exec (N 'select top' + @ topn
+ N' + @ fieldshow
+ N' from' + @ tbname
+ N' a left join (select top '+ @ topn1
+ N' + @ fieldkey
+ N' from' + @ tbname
+ N'a' + @ where
+ N' + @ fieldorder
+ N') B on' + @ where1
+ N'' + @ where2
+ N'' + @ fieldorder)
End