USP_DESIGNATIONCMS_VIEWDATA

create procedure dbo.USP_DESIGNATIONCMS_VIEWDATA
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @VANITYNAME nvarchar(512) = null output,
                    @URL nvarchar(max) = null output,
                    @CMSCHILDREN nvarchar(max) = null output,
                    @STARTDATE datetime = null output,
                    @ENDDATE datetime = null output,
                    @ISACTIVE bit = null output, 
                    @CMS bit = null output,
                    @ISLIVE bit = null output,
                    @SITEPAGESID int = null output
                )
                as
                begin
                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @VANITYNAME = DESIGNATION.VANITYNAME,
                        @URL = CASE WHEN ISNULL(CAST(MP.SITEPAGESID AS BIT), 0) = 1 THEN dbo.UFN_BBNC_URL() + (SELECT TOP 1 VanityURL from dbo.VanityURL where PageID= MP.SITEPAGESID) ELSE '' END, --DESIGNATION.USERID CAST(@ID AS nvarchar(36)), 

                        @CMSCHILDREN = CASE WHEN ISNULL(CAST(MP.SITEPAGESID AS BIT), 0) = 1 THEN (
                            select dbo.UDA_BUILDLIST(D.VANITYNAME) --aggregate function to concatenate table row values into a semicolon-delimited string (though not strictly concatenation as order is up to the vagaries of the optimizer)

                            from dbo.DESIGNATION D 
                            inner join dbo.DESIGNATIONLEVEL DL
                                on ((D.DESIGNATIONLEVEL1ID = DL.ID) or (D.DESIGNATIONLEVEL2ID = DL.ID) or (D.DESIGNATIONLEVEL3ID = DL.ID) or (D.DESIGNATIONLEVEL4ID = DL.ID) or (D.DESIGNATIONLEVEL5ID = DL.ID))
                            inner join dbo.MICROSITEPAGE MP 
                                on (MP.OBJECTID = D.ID) and (MP.EXCLUDED = 0)
                            where (DL.ID = 
                                case 
                                    when DESIGNATION.DESIGNATIONLEVEL2ID is null then DESIGNATION.DESIGNATIONLEVEL1ID
                                    when DESIGNATION.DESIGNATIONLEVEL3ID is null then DESIGNATION.DESIGNATIONLEVEL2ID
                                    when DESIGNATION.DESIGNATIONLEVEL4ID is null then DESIGNATION.DESIGNATIONLEVEL3ID
                                    when DESIGNATION.DESIGNATIONLEVEL5ID is null then DESIGNATION.DESIGNATIONLEVEL4ID
                                    else DESIGNATION.DESIGNATIONLEVEL5ID
                                end)
                            --and not (D.ID = DESIGNATION.ID) per conversation with Matt 11/23, the list should include itself

                            and (D.ISACTIVE = 1)
                            and (((GETDATE() >= D.STARTDATE) or (D.STARTDATE is null)) and ((GETDATE() <= D.ENDDATE) or (D.ENDDATE is null)))
                        ) ELSE '' END,
                        @STARTDATE = DESIGNATION.STARTDATE,
                        @ENDDATE = DESIGNATION.ENDDATE,
                        @ISACTIVE = DESIGNATION.ISACTIVE, 
                        @CMS = ISNULL(CAST(MP.SITEPAGESID AS BIT), 0),
                        @ISLIVE = CASE
                            WHEN
                                (ISNULL(CAST(MP.SITEPAGESID AS BIT), 0) = 1)
                                and (DESIGNATION.ISACTIVE = 1)
                                and (((GETDATE() >= DESIGNATION.STARTDATE) or (DESIGNATION.STARTDATE is null)) and ((GETDATE() <= DESIGNATION.ENDDATE) or (DESIGNATION.ENDDATE is null)))
                                THEN 1 
                            ELSE 0 END,
                        @SITEPAGESID = MP.SITEPAGESID
                    from dbo.DESIGNATION
                    left join dbo.MICROSITEPAGE MP on (MP.OBJECTID = DESIGNATION.ID) and (MP.EXCLUDED = 0)
                    where DESIGNATION.ID = @ID;        
                    return 0;
                end