SQL分页存储过程 支持连接查询等复杂的SQL

CREATE PROCEDURE Pagination
(
 @SQL nvarchar(1024),   
 @PageSize int = 20,    --分页大小
 @PageIndex int = 0,    --分页索引
 @Sort nvarchar(100) = '''',    --排序字段
 @TotalCount int = 0 output --总数   
)
AS

set nocount on
/*声明查询字符串*/
declare @strSQL nvarchar(4000)

set @strSQL = '' select @TotalCount=count(*) from (''+@SQL+'') as t ''

/*取得查询结果总数*/
exec sp_executesql
@strSQL,
N''@TotalCount int=0 OUTPUT'',
@TotalCount=@TotalCount OUTPUT

declare @ItemCount int
declare @_PageIndex int

set @_PageIndex = @PageIndex + 1;
/*确定搜索边界*/
set @ItemCount = @TotalCount - @PageSize * @_PageIndex

if(@ItemCount < 0)
    set @ItemCount = @ItemCount + @PageSize
else
    set @ItemCount = @PageSize

if(@ItemCount < 0) return 1

if(@Sort != '''')
begin
    /*声明排序变量*/
    declare @IndexSort1 nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2 nvarchar(50)
   
    SET @Sort1 = @Sort
    SET @Sort2 = Replace(Replace(Replace(@Sort, ''DESC'', ''@SORT''), ''ASC'', ''DESC''), ''@SORT'', ''ASC'')
 
    print @Sort1
    print @Sort2

    set @strSQL = ''SELECT * FROM
    (SELECT TOP '' + STR(@ItemCount) + '' * FROM
    (SELECT TOP '' + STR(@PageSize * @_PageIndex) + '' * FROM
    (''+@SQL+'') AS t0
    ORDER BY ''+@Sort1 +'') AS t1
    ORDER BY ''+@Sort2 +'') AS t2
    ORDER BY '' +@Sort
end
else
begin
    set @strSQL = ''SELECT * FROM
    (SELECT TOP '' + STR(@ItemCount)