USP_SIMPLEDATALIST_QUERYCATEGORYINUSE

CREATE procedure dbo.USP_SIMPLEDATALIST_QUERYCATEGORYINUSE(@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-000000000000',
        [LABEL] = '<none>'
    where exists(select ID from dbo.ADHOCQUERY where QUERYCATEGORYCODEID is null)

    union

    --ADHOC QUERIES

    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 */

    union

    --SMARTQUERIES

    select distinct QUERYCATEGORYCODE.ID as [VALUE],
        QUERYCATEGORYCODE.DESCRIPTION as [LABEL]
    from dbo.QUERYCATEGORYCODE
    inner join dbo.SMARTQUERYINSTANCE on QUERYCATEGORYCODE.ID = SMARTQUERYINSTANCE.QUERYCATEGORYCODEID
    where
    (@ONLYMOBILIZED = 0 or SMARTQUERYINSTANCE.MOBILIZE = 1)
    and
        (@ISSYSADMIN = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID,SMARTQUERYINSTANCE.ID) = 1)

    order by [LABEL];