UFN_APPEALDESIGNATION_SITE_GET

create function dbo.UFN_APPEALDESIGNATION_SITE_GET
(
  @EVENTID uniqueidentifier,
  @LEVEL int = 1
)
returns table 
as 
  return 
    select A.SITEID as APPEALSITEID, coalesce(DL1.SITEID, DL2.SITEID, DL3.SITEID, DL4.SITEID, DL5.SITEID) AS DESIGNATIONSITEID
    from dbo.EVENT E
    join dbo.APPEAL A on E.APPEALID = A.ID
    join dbo.APPEALDESIGNATION AD on A.ID = AD.APPEALID and AD.ISDEFAULT = 1 
    join dbo.DESIGNATION D on D.ID = AD.DESIGNATIONID
    left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = D.DESIGNATIONLEVEL1ID and @LEVEL = 1
    left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = D.DESIGNATIONLEVEL2ID and @LEVEL = 2
    left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = D.DESIGNATIONLEVEL3ID and @LEVEL = 3
    left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = D.DESIGNATIONLEVEL4ID and @LEVEL = 4
    left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = D.DESIGNATIONLEVEL5ID and @LEVEL = 5
    where E.ID = @EVENTID