USP_SIMPLEDATALIST_EVENTCATEGORYCODE

CREATE procedure dbo.USP_SIMPLEDATALIST_EVENTCATEGORYCODE
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as

    select 
        ID as VALUE, 
        DESCRIPTION as LABEL
    from dbo.EVENTCATEGORYCODE
    where (
            select 
                count(*) 
            from 
                dbo.EVENTCATEGORYCODESITE 
            where 
                EVENTCATEGORYCODESITE.EVENTCATEGORYCODEID = EVENTCATEGORYCODE.ID
        ) = 0
        or    
        (
            select
                count(*)
            from
                dbo.EVENTCATEGORYCODESITE
            where
                EVENTCATEGORYCODESITE.EVENTCATEGORYCODEID = EVENTCATEGORYCODE.ID
            and
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTCATEGORYCODESITE].[SITEID] or (SITEID is null and [EVENTCATEGORYCODESITE].[SITEID] is null)))
        ) > 0

    union all

    select 
        ID as VALUE, 
        DESCRIPTION as LABEL
    from dbo.PROGRAMCATEGORYCODE
    where (
            select 
                count(*) 
            from 
                dbo.PROGRAMCATEGORYCODESITE 
            where 
                PROGRAMCATEGORYCODESITE.PROGRAMCATEGORYCODEID = PROGRAMCATEGORYCODE.ID
        ) = 0
        or    
        (
            select
                count(*)
            from
                dbo.PROGRAMCATEGORYCODESITE as EVENTCATEGORYCODESITE
            where
                EVENTCATEGORYCODESITE.PROGRAMCATEGORYCODEID = PROGRAMCATEGORYCODE.ID
            and
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTCATEGORYCODESITE].[SITEID] or (SITEID is null and [EVENTCATEGORYCODESITE].[SITEID] is null)))
        ) > 0

    order by DESCRIPTION