USP_BBNC_GETDESIGNATION

            CREATE procedure dbo.USP_BBNC_GETDESIGNATION(@BBNCID int)
            as
            set nocount on;

                select
                    [MAP].ID as [BBNCID],
                    [MAP].DESIGNATIONID as [DESIGNATIONID],
                    [ADDEDBY].USERNAME as [ADDEDBY],
                    [CHANGEDBY].USERNAME as [CHANGEDBY],
                    DESIGNATION.DATEADDED,
                    DESIGNATION.DATECHANGED,
                    replace(DESIGNATION.NAME,' \ ',' / ') as [LONGDESCRIPTION], 
                    DESIGNATION.USERID as [SHORTDESCRIPTION],
                    DESIGNATION.ISACTIVE,
                    case
                        when [LEVEL1].[ID] is not null and [LEVEL2].[ID] is not null and [LEVEL3].[ID] is not null and [LEVEL4].[ID] is not null and [LEVEL5].[ID] is not null then
                            coalesce([CATEGORY1].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY2].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY3].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY4].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY5].[DESCRIPTION], '-')
                        when [LEVEL1].[ID] is not null and [LEVEL2].[ID] is not null and [LEVEL3].[ID] is not null and [LEVEL4].[ID] is not null and [LEVEL5].[ID] is null then
                            coalesce([CATEGORY1].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY2].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY3].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY4].[DESCRIPTION], '-')
                        when [LEVEL1].[ID] is not null and [LEVEL2].[ID] is not null and [LEVEL3].[ID] is not null and [LEVEL4].[ID] is null and [LEVEL5].[ID] is null then
                            coalesce([CATEGORY1].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY2].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY3].[DESCRIPTION], '-')
                        when [LEVEL1].[ID] is not null and [LEVEL2].[ID] is not null and [LEVEL3].[ID] is null and [LEVEL4].[ID] is null and [LEVEL5].[ID] is null then
                            coalesce([CATEGORY1].[DESCRIPTION], '-') + ' / ' + coalesce([CATEGORY2].[DESCRIPTION], '-')
                        when [LEVEL1].[ID] is not null and [LEVEL2].[ID] is null and [LEVEL3].[ID] is null and [LEVEL4].[ID] is null and [LEVEL5].[ID] is null then
                            coalesce([CATEGORY1].[DESCRIPTION], '-')
                        else
                            null
                    end as [CATEGORY],
                    DESIGNATION.VANITYNAME
                from dbo.BBNCDESIGNATIONIDMAP as [MAP]
                inner join dbo.DESIGNATION on DESIGNATION.ID = [MAP].DESIGNATIONID
                inner join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = DESIGNATION.ADDEDBYID
                inner join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = DESIGNATION.CHANGEDBYID
                left join DESIGNATIONLEVEL as [LEVEL1] on [LEVEL1].ID = [DESIGNATION].DESIGNATIONLEVEL1ID
                left join DESIGNATIONLEVEL as [LEVEL2] on [LEVEL2].ID = [DESIGNATION].DESIGNATIONLEVEL2ID
                left join DESIGNATIONLEVEL as [LEVEL3] on [LEVEL3].ID = [DESIGNATION].DESIGNATIONLEVEL3ID
                left join DESIGNATIONLEVEL as [LEVEL4] on [LEVEL4].ID = [DESIGNATION].DESIGNATIONLEVEL4ID
                left join DESIGNATIONLEVEL as [LEVEL5] on [LEVEL5].ID = [DESIGNATION].DESIGNATIONLEVEL5ID
                left join dbo.DESIGNATIONLEVELCATEGORYCODE as [CATEGORY1] on [CATEGORY1].ID = [LEVEL1].DESIGNATIONLEVELCATEGORYCODEID
                left join dbo.DESIGNATIONLEVELCATEGORYCODE as [CATEGORY2] on [CATEGORY2].ID = [LEVEL2].DESIGNATIONLEVELCATEGORYCODEID
                left join dbo.DESIGNATIONLEVELCATEGORYCODE as [CATEGORY3] on [CATEGORY3].ID = [LEVEL3].DESIGNATIONLEVELCATEGORYCODEID
                left join dbo.DESIGNATIONLEVELCATEGORYCODE as [CATEGORY4] on [CATEGORY4].ID = [LEVEL4].DESIGNATIONLEVELCATEGORYCODEID
                left join dbo.DESIGNATIONLEVELCATEGORYCODE as [CATEGORY5] on [CATEGORY5].ID = [LEVEL5].DESIGNATIONLEVELCATEGORYCODEID
                where [MAP].ID = @BBNCID