Sql Server排序分页

1 DECLARE @PageIndex INT = 1
2 DECLARE @PageSize INT = 10
3 
4 SELECT * FROM [T_Student] 
5 ORDER BY [iCreatedOn] DESC 
6 OFFSET ((@PageIndex-1)*@PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY

 1 ALTER PROC [dbo].[p_pagination]
 2 @Sql NVARCHAR(MAX),          --自定义查询sql语句
 3 @SortField NVARCHAR(MAX),    --分页-排序字段
 4 @IsAscending  BIT,           --分页-0正序/1倒序
 5 @PageSize INT,               --分页-每页数量
 6 @PageIndex INT,              --分页-第几页
 7 @TotalRecord INT OUTPUT      --分页-总数
 8 AS
 9 
10 DECLARE @SqlString NVARCHAR(MAX)
11 SET @SqlString = 'SELECT @TotalRecord=COUNT(*) FROM ( {{sql}} ) AS T0'
12 SET @SqlString=REPLACE(@SqlString,'{{sql}}',@Sql)
13 EXEC sp_executesql @SqlString,N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT
14 PRINT '总数：'+ CONVERT(NVARCHAR(MAX), @TotalRecord) 
15 
16 SET @SqlString = 'SELECT * FROM ( {{sql}} ) AS T0 ORDER BY {{orderby}}{{isascending}} OFFSET {{offset}} ROWS FETCH NEXT {{pagesize}} ROWS only'
17 SET @SqlString=REPLACE(@SqlString,'{{sql}}',@Sql)
18 SET @SqlString=REPLACE(@SqlString,'{{orderby}}',@SortField)
19 SET @SqlString=REPLACE(@SqlString,'{{isascending}}',CASE WHEN @IsAscending=0 THEN '' ELSE ' DESC' END)
20 SET @SqlString=REPLACE(@SqlString,'{{offset}}',(@PageIndex-1)*@PageSize)
21 SET @SqlString=REPLACE(@SqlString,'{{pagesize}}',@PageSize)
22 EXEC sp_executesql @SqlString
23 
24 --调用
25 --DECLARE @TotalRecord INT
26 --EXEC [dbo].[p_pagination] 'SELECT * FROM T_User','CreateTime',0,10,1,@TotalRecord OUTPUT
27 --PRINT @TotalRecord