| The code is as follows: |
Copy code |
If object_ID ('[proc_SelectForPager]') is not null
Drop Procedure [proc_SelectForPager]
Go
Create Proc proc_SelectForPager
(
@ SQL varchar (max ),
@ Order varchar (4000 ),
@ CurrentPage int,
@ PageSize int,
@ TotalCount int output
)
As
/* Andy 2012-2-28 */
Declare @ Exec_ SQL nvarchar (max)
Set @ Exec_ SQL = 'set @ TotalCount = (Select Count (1) From ('+ @ SQL +') As )'
Exec sp_executesql @ Exec_ SQL, n' @ TotalCount int output', @ TotalCount output
Set @ Order = isnull ('Order by' + nullif (@ Order, ''), 'Order by getdate ()')
If @ CurrentPage = 1/* the 1st page is often called. Special processing is performed here, with a lower layer of subquery */
Set @ Exec_ SQL ='
; With CTE_Exec
(
'+ @ SQL +'
)
Select Top (@ pagesize) *, row_number () Over ('+ @ Order +') As r From CTE_Exec Order By r
'
Else
Set @ Exec_ SQL ='
; With CTE_Exec
(
Select *, row_number () Over ('+ @ Order +') As r From ('+ @ SQL +') As
)
Select * From CTE_Exec Where r Between (@ CurrentPage-1) * @ pagesize + 1 And @ CurrentPage * @ pagesize Order By r
'
Exec sp_executesql @ Exec_ SQL, n' @ CurrentPage int, @ PageSize int ', @ CurrentPage, @ PageSize
Go |
Call method:
1. Single table:
| The code is as follows: |
Copy code |
Exec proc_SelectForPager @ SQL = 'select * from contacts a where a. ContactType = 1', -- varchar (max)
@ Order = '', -- varchar (4000)
@ CurrentPage = 3, -- int
@ PageSize = 20, -- int
@ TotalCount = 0 -- int2. multi-table join:
Exec proc_SelectForPager @ SQL =
'Select a. Staff, a. OU, B. FName + B. FName as Name
From staffOUHIST
Inner join Staff B on B. ID = a. Staff and a. ExpiryDate = '20180101''
', -- Varchar (max)
@ Order = '', -- varchar (4000)
@ CurrentPage = 3, -- int
@ PageSize = 20, -- int
@ TotalCount = 0 -- int
|
Note: CTE cannot be used in @ SQL.
