USP_DATALIST_DESIGNATIONNAVIGATIONTREE

CREATE procedure dbo.USP_DATALIST_DESIGNATIONNAVIGATIONTREE (@DESIGNATIONLEVEL1ID uniqueidentifier)
as
set nocount on;

declare @DESIGNATIONPAGEID nvarchar(36) = '158571a0-52d3-4a27-9d30-0dbbfa9f1386';
declare @PURPOSEPAGEID nvarchar(36) = 'f5f8f158-73ab-40ec-b15f-50aa0030290c';

select DL1.[NAME] as CAPTION
  ,case 
    when D1.SYSTEMGENERATED = 1
      then @PURPOSEPAGEID
    else @DESIGNATIONPAGEID
    end as PAGEID
  ,case 
    when D1.SYSTEMGENERATED = 1
      then DL1.ID
    else D1.ID
    end CONTEXTID
  ,(
    select DL2.[NAME] as CAPTION
      ,case 
        when D2.SYSTEMGENERATED = 1
          then @PURPOSEPAGEID
        else @DESIGNATIONPAGEID
        end as PAGEID
      ,case 
        when D2.SYSTEMGENERATED = 1
          then DL2.ID
        else D2.ID
        end CONTEXTID
      ,(
        select DL3.[NAME] as CAPTION
          ,case 
            when D3.SYSTEMGENERATED = 1
              then @PURPOSEPAGEID
            else @DESIGNATIONPAGEID
            end as PAGEID
          ,case 
            when D3.SYSTEMGENERATED = 1
              then DL3.ID
            else D3.ID
            end CONTEXTID
          ,(
            select DL4.[NAME] as CAPTION
              ,case 
                when D4.SYSTEMGENERATED = 1
                  then @PURPOSEPAGEID
                else @DESIGNATIONPAGEID
                end as PAGEID
              ,case 
                when D4.SYSTEMGENERATED = 1
                  then DL4.ID
                else D4.ID
                end CONTEXTID
              ,(
                select DL5.[NAME] as CAPTION
                  ,case 
                    when D5.SYSTEMGENERATED = 1
                      then @PURPOSEPAGEID
                    else @DESIGNATIONPAGEID
                    end as PAGEID
                  ,case 
                    when D5.SYSTEMGENERATED = 1
                      then DL5.ID
                    else D5.ID
                    end CONTEXTID
                from dbo.DESIGNATION as D5
                inner join dbo.DESIGNATIONLEVEL as DL5 on D5.DESIGNATIONLEVEL5ID = DL5.ID
                where (D5.DESIGNATIONLEVEL1ID = D1.DESIGNATIONLEVEL1ID)
                  and (D5.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID)
                  and (D5.DESIGNATIONLEVEL3ID = D3.DESIGNATIONLEVEL3ID)
                  and (D5.DESIGNATIONLEVEL4ID = D4.DESIGNATIONLEVEL4ID)
                order by DL5.[NAME]
                for xml raw('NODE')
                  ,root('NODES')
                  ,elements
                  ,type
                )
            from dbo.DESIGNATION as D4
            inner join dbo.DESIGNATIONLEVEL as DL4 on D4.DESIGNATIONLEVEL4ID = DL4.ID
            where (D4.DESIGNATIONLEVEL1ID = D1.DESIGNATIONLEVEL1ID)
              and (D4.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID)
              and (D4.DESIGNATIONLEVEL3ID = D3.DESIGNATIONLEVEL3ID)
              and (D4.DESIGNATIONLEVEL5ID is null)
            order by DL4.[NAME]
            for xml raw('NODE')
              ,root('NODES')
              ,elements
              ,type
            )
        from dbo.DESIGNATION as D3
        inner join dbo.DESIGNATIONLEVEL as DL3 on D3.DESIGNATIONLEVEL3ID = DL3.ID
        where (D3.DESIGNATIONLEVEL1ID = D1.DESIGNATIONLEVEL1ID)
          and (D3.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID)
          and (D3.DESIGNATIONLEVEL4ID is null)
        order by DL3.[NAME]
        for xml raw('NODE')
          ,root('NODES')
          ,elements
          ,type
        )
    from dbo.DESIGNATION as D2
    inner join dbo.DESIGNATIONLEVEL as DL2 on D2.DESIGNATIONLEVEL2ID = DL2.ID
    where (D2.DESIGNATIONLEVEL1ID = D1.DESIGNATIONLEVEL1ID)
      and (D2.DESIGNATIONLEVEL3ID is null)
    order by DL2.[NAME]
    for xml raw('NODE')
      ,root('NODES')
      ,elements
      ,type
    ) as NODES
from dbo.DESIGNATION as D1
inner join dbo.DESIGNATIONLEVEL as DL1 on D1.DESIGNATIONLEVEL1ID = DL1.ID
where (D1.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVEL1ID)
  and (D1.DESIGNATIONLEVEL2ID is null)