USP_SIMPLEDATALIST_ADHOCQUERYCATEGORYINUSE

CREATE procedure dbo.USP_SIMPLEDATALIST_ADHOCQUERYCATEGORYINUSE(@CURRENTAPPUSERID uniqueidentifier,
    @ONLYMOBILIZED bit = 0)
as
    set nocount on;

    declare @ISSYSADMIN bit;
    declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;

    select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;

    if @ISSYSADMIN = 0
        select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f');

    /* Filter out any ad-hoc queries involving query views to which the current user does not have rights */
    if (@ISSYSADMIN = 0 and @HASQUERYVIEWSYSTEMPRIVILEGE = 0)
    begin
        declare @OKVIEWS table (OBJECTNAME nvarchar(128));

        insert into @OKVIEWS (OBJECTNAME)
        select QUERYVIEWCATALOG.OBJECTNAME from dbo.QUERYVIEWCATALOG
        where exists (
            select [OKVIEWS].QUERYVIEWCATALOGID 
            from dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@CURRENTAPPUSERID) as [OKVIEWS] where [OKVIEWS].QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID);
    end;

    select [VALUE] = '00000000-0000-0000-0000-000000000001',
        [LABEL] = '<None>'
    where exists(select ID from dbo.ADHOCQUERY where QUERYCATEGORYCODEID is null)
    union
    select distinct QUERYCATEGORYCODE.ID as [VALUE],
        QUERYCATEGORYCODE.DESCRIPTION as [LABEL]
    from dbo.QUERYCATEGORYCODE
    inner join dbo.ADHOCQUERY on QUERYCATEGORYCODE.ID = ADHOCQUERY.QUERYCATEGORYCODEID
    where
    (@ONLYMOBILIZED = 0 or ADHOCQUERY.MOBILIZE = 1)
    and
        /* Filter out restricted query views */
        ((@ISSYSADMIN = 1 or @HASQUERYVIEWSYSTEMPRIVILEGE = 1)
            /* This will return a list of query views in use by the ad-hoc query but not in the OK views table variable; 
            These ad-hoc queries should not be returned to the client. */
            or not exists(select [QUERYVIEWSINUSE].OBJECTNAME 
                from dbo.UFN_ADHOCQUERY_QUERYVIEWSINUSE(ADHOCQUERY.QUERYDEFINITIONXML) as [QUERYVIEWSINUSE]
                where not exists (
                    select [OKVIEWS].OBJECTNAME 
                    from @OKVIEWS as [OKVIEWS] where [OKVIEWS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME)))
        /* End restricted query views filter */
    order by [LABEL];