This is a recently written paging stored procedure that can be applied to SQL Server 2005:
Copy Code code as follows:
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 a) '
Exec sp_executesql @Exec_sql, N ' @TotalCount int output ', @TotalCount output
Set @Order =isnull (' ORDER by ' +nullif (@Order, "), ' Order by GETDATE () ')
If @CurrentPage = 1/* will often call page 1th, here to do special processing, less a layer of subquery * *
Set @Exec_sql = '
; With Cte_exec as
(
' + @Sql + '
)
Select Top (@pagesize) *,row_number () over (' + @Order + ') as R-Cte_exec order by R
'
Else
Set @Exec_sql = '
; With Cte_exec as
(
Select *,row_number () over (' + @Order + ') as r from (' + @Sql + ') as a
)
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：
Copy Code code as follows:
Exec Proc_selectforpager @Sql = ' Select * from contacts a where a.contacttype=1 ',-varchar (max)
@Order = ",--varchar (4000)
@CurrentPage = 3,--int
@PageSize =--INT
@TotalCount = 0--int
2. Multi-table Join：
Copy Code code as follows:
Exec Proc_selectforpager @Sql =
' Select A.staff,a.ou,b.fname+b.fname as Name
From Staffouhist A
INNER JOIN Staff B on B.id=a.staff and a.expirydate= ' 30001231 '
',--varchar (max)
@Order = ",--varchar (4000)
@CurrentPage = 3,--int
@PageSize =--INT
@TotalCount = 0--int
Note: You cannot use a CTE in @sql.
