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