USP_DATALIST_DESIGNATION_HIERARCHY

                CREATE procedure dbo.USP_DATALIST_DESIGNATION_HIERARCHY
                (
                    @DESIGNATIONID uniqueidentifier = null,
                    @DESIGNATIONLEVEL1ID uniqueidentifier = null,
                    @ACTIVEONLY bit = null
                ) as

                set nocount on;

                if @DESIGNATIONID is null
                    select D.ID,
                            DL.ID DESIGNATIONLEVELID,
                            DL.NAME,
                            FPT.DESCRIPTION DESIGNATIONLEVELTYPE,
                            D.ISACTIVE,
                            (select count(DES.ID) from dbo.DESIGNATION DES
                             where DES.DESIGNATIONLEVEL1ID = D.DESIGNATIONLEVEL1ID) CHILDCOUNT
                    from dbo.DESIGNATION D
                    inner join dbo.DESIGNATIONLEVEL DL on D.DESIGNATIONLEVEL1ID = DL.ID
                    inner join dbo.DESIGNATIONLEVELTYPE FPT on DL.DESIGNATIONLEVELTYPEID = FPT.ID
                    where (D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVEL1ID or @DESIGNATIONLEVEL1ID is null) and
                        D.DESIGNATIONLEVEL2ID is null and
                        (D.ISACTIVE = 1 or @ACTIVEONLY is null)

                else
                    begin

                    declare @DL1 uniqueidentifier;
                    declare @DL2 uniqueidentifier;
                    declare @DL3 uniqueidentifier;
                    declare @DL4 uniqueidentifier;
                    declare @DL5 uniqueidentifier;
                    declare @LEVEL int;

                    select @DL1 = DESIGNATIONLEVEL1ID,
                            @DL2 = DESIGNATIONLEVEL2ID,
                            @DL3 = DESIGNATIONLEVEL3ID,
                            @DL4 = DESIGNATIONLEVEL4ID,
                            @DL5 = DESIGNATIONLEVEL5ID,
                            @LEVEL = case when DESIGNATIONLEVEL2ID is null then 2
                                        when DESIGNATIONLEVEL3ID is null then 3
                                        when DESIGNATIONLEVEL4ID is null then 4
                                        else 5
                                    end 
                    from dbo.DESIGNATION
                    where ID = @DESIGNATIONID;

                    select D.ID,
                            DL.ID DESIGNATIONLEVELID,
                            DL.NAME,
                            FPT.DESCRIPTION DESIGNATIONLEVELTYPE,
                            D.ISACTIVE,
                            case when exists (select top 1 DES.ID from dbo.DESIGNATION DES
                             where DES.ID <> D.ID and DES.DESIGNATIONLEVEL1ID = D.DESIGNATIONLEVEL1ID and
                                    (DES.DESIGNATIONLEVEL2ID = D.DESIGNATIONLEVEL2ID) and
                                   (DES.DESIGNATIONLEVEL3ID = D.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null) and
                                   (DES.DESIGNATIONLEVEL4ID = D.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null) and
                                   (DES.DESIGNATIONLEVEL5ID = D.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                            then 2 else 0 end
                            as CHILDCOUNT
                    from dbo.DESIGNATION as D
                    inner join dbo.DESIGNATIONLEVEL DL 
                        on DL.ID = 
                        case @LEVEL
                            when 2 then D.DESIGNATIONLEVEL2ID
                            when 3 then D.DESIGNATIONLEVEL3ID
                            when 4 then D.DESIGNATIONLEVEL4ID
                            when 5 then D.DESIGNATIONLEVEL5ID
                        end    
                    inner join dbo.DESIGNATIONLEVELTYPE FPT on DL.DESIGNATIONLEVELTYPEID = FPT.ID
                    where
                        ((D.DESIGNATIONLEVEL1ID = @DL1) and
                        ((@LEVEL = 2 and D.DESIGNATIONLEVEL3ID is null) or 
                        (@LEVEL = 3 and D.DESIGNATIONLEVEL2ID = @DL2 and D.DESIGNATIONLEVEL4ID is null) or
                        (@LEVEL = 4 and D.DESIGNATIONLEVEL2ID = @DL2 and D.DESIGNATIONLEVEL3ID = @DL3 and D.DESIGNATIONLEVEL5ID is null) or
                        (@LEVEL = 5 and D.DESIGNATIONLEVEL4ID = @DL4 and D.DESIGNATIONLEVEL3ID = @DL3 and D.DESIGNATIONLEVEL2ID = @DL2)))
                    end