SQL 存储过程编写分页（子父级显示）

USE [PrettyCloud]
GO
/****** Object:  StoredProcedure [dbo].[GetRecursionListByPage]    Script Date: 04/20/2018 14:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
功能：分页获取组织列表
*/
ALTER Procedure [dbo].[GetRecursionListByPage]
(
    @tblName nvarchar(300), ----要显示的表或多个表的连接
    @fldName nvarchar(500) = '*', ----要显示的字段列表
    @pageSize int = 10, ----每页显示的记录个数 
    @page int = 1, ----要显示那一页的记录 
    @fldSort nvarchar(200) = null, ----排序字段列表或条件
    @Sort bit = 0, ----排序方法，0为升序，1为降序
    @strCondition nvarchar(1000) = null, ----查询条件,不需where 
    @pageCount int = 1 output, ----查询结果分页后的总页数 
    @Counts int = 1 output ----查询到的记录数 
)
as
begin
    SET NOCOUNT ON 
    Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句 
    Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句 
    Declare @strSortType nvarchar(10) ----数据排序规则A 
    Declare @strFSortType nvarchar(10) ----数据排序规则B
    Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
    Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
    
    set @SqlSelect = 'select '
    set @SqlCounts = 'Count(*)' 
        
    if @Sort=0  ---是否排序
    begin
        set @strFSortType=' ASC ' 
        set @strSortType=' ASC '
    end
    else
    begin
        set @strFSortType=' DESC '
        set @strSortType=' DESC ' 
    end

    if @strCondition is null or @strCondition='' --没有设置显示条件
    begin
        set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName+' Where ISNULL(ParentId,''0'')=''0''' 
    end
    else
    begin
        set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName+' Where ISNULL(ParentId,''0'')=''0'''+ @strCondition
    end
    ----取得查询结果总数量----- 
    exec sp_executesql @strTmp,N'@Counts int out ',@Counts out 
    
    declare @tmpCounts int 
    if @Counts = 0
    begin
        set @tmpCounts = 1 
    end
    else
    begin
        set @tmpCounts = @Counts
    end
    --取得分页总数 
    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 
    
    declare @mainSql  nvarchar(max)=''
    declare @rebuildfldName varchar(3000)
    set @rebuildfldName=@tblName+'.'+REPLACE(@fldName,',',','+@tblName+'.')
    
    set @mainSql='With org '    
    set @mainSql=@mainSql+' as '    
    set @mainSql=@mainSql+'( '    
    set @mainSql=@mainSql+@SqlSelect+@fldName+' from '
        set @mainSql=@mainSql+' ( '
        set @mainSql=@mainSql+@SqlSelect+' ROW_NUMBER() Over(order by '+ @fldSort+' '+ @strFSortType+') as num,'
        set @mainSql=@mainSql+@fldName
        set @mainSql=@mainSql+' from '+@tblName+' Where ISNULL(ParentId,''0'')=''0'' and ISNULL(DeleteMark,0)=0'
        set @mainSql=@mainSql+' ) tempTable '
        set @mainSql=@mainSql+' where num between '+str((@Page-1)*@PageSize+1)+' and '+str(@Page*@PageSize)
    set @mainSql=@mainSql+' union all '    
    set @mainSql=@mainSql+@SqlSelect+' '+@rebuildfldName
    set @mainSql=@mainSql+' from org'
    set @mainSql=@mainSql+' join '+@tblName
    set @mainSql=@mainSql+' on org.Id='+@tblName+'.ParentId'        
    set @mainSql=@mainSql+')'    
    set @mainSql=@mainSql+@SqlSelect+@fldName
    set @mainSql=@mainSql+' from org where 1=1'    
    set @mainSql=@mainSql+@strCondition
    
    ------返回查询结果----- 
    exec sp_executesql @mainSql 
    print @mainSql 
    SET NOCOUNT OFF    
end