UFN_BBNC_URLFORUSER

CREATE function dbo.UFN_BBNC_URLFORUSER(@CLIENTUSERID int)
returns nvarchar(1024)
with execute as caller
as begin

    declare @DEFAULT int = (select Value from dbo.Setting where ID = 1);
    declare @TARGETSITE int = (select top 1 DefaultSiteID from dbo.Clientusers where ID = @CLIENTUSERID);

  if(@TARGETSITE IS NULL)
    begin
      select @TARGETSITE = SITEID
      from
      (
          select top 1 isnull(SA.CLIENTSITESID, @DEFAULT) as SITEID, case isnull(SA.CLIENTSITESID, @DEFAULT) when @DEFAULT then 1 else 0 end as ISDEFAULT
          from dbo.UserRoles UR
          inner join dbo.CMSSECURITYGROUPCLIENTROLECLIENTSITE SA on SA.CLIENTROLESID = UR.ClientRolesID
          inner join dbo.CMSSECURITYGROUP CSG on CSG.ID = SA.CMSSECURITYGROUPID and CSG.SITETASKSGROUP = 1
          where UR.ClientUsersID = @CLIENTUSERID 
          order by ISDEFAULT desc
      ) a

      select @TARGETSITE = @DEFAULT
      from dbo.clientusers CU
      where CU.ID = @CLIENTUSERID and (CU.InternalUser = 1 Or CU.IsSupervisor = 1)
  end

  if(@TARGETSITE IS NULL) select @TARGETSITE = @DEFAULT; 

    return (select VALUE from dbo.CMSSITESETTING where CLIENTSITESID = @TARGETSITE and ENUMID = 11)
end