USP_DATALIST_CMSDESIGNATION

CREATE procedure dbo.USP_DATALIST_CMSDESIGNATION(
  @SEARCHCONTEXT nvarchar(100) = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

  declare @ISSYSADMIN bit;
    select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

    select 
    d.ID,
    d.DESIGNATION AS HIERARCHY,               
        d.USERID AS LOOKUPID, 
    d.VANITYNAME AS PUBLICNAME,
    map.ID as CMSID
    from 
    dbo.V_SEARCH_DESIGNATION d 
      inner join dbo.BBNCDESIGNATIONIDMAP map on map.DESIGNATIONID = d.ID
    where 
    ISACTIVE=1 
    and
    (
          -- SHL Enterprise Bug 252498; If the user is an admin, they should be able to see the designations without sites

          -- else, users specified to sites shold only see those sites

          (@ISSYSADMIN = 1 and d.[SITEID] is null)
          or
          (
             @ISSYSADMIN = 1 --either system admin

                or 
             exists(select SYSTEMROLEAPPUSER.ID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID and SYSTEMROLEAPPUSER.SECURITYMODECODE = 0)  --Or have access to view all records, requires when there is no sites in system but user can view all records. 

                or  
             exists (select 1 from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID) where SITEID=d.[SITEID]) --Or based on site access

           )
        )
    and d.ISREVENUEDESIGNATION = 1
    and (@SEARCHCONTEXT IS NULL OR @SEARCHCONTEXT='' OR  d.DESIGNATION LIKE '%' + @SEARCHCONTEXT +'%' OR d.USERID LIKE '%' + @SEARCHCONTEXT +'%'  OR d.VANITYNAME LIKE '%' + @SEARCHCONTEXT +'%')