USP_ADHOCQUERY_GETLIST

CREATE procedure dbo.USP_ADHOCQUERY_GETLIST(@CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier = null, @QUERYCATEGORYCODEID uniqueidentifier = null)
with execute as caller
as
    set nocount on;

    declare @QUERYCATEGORYCODEIDNOTNULL as uniqueidentifier;
    declare @RECORDTYPEIDNOTNULL as uniqueidentifier;

    if @RECORDTYPEID = '00000000-0000-0000-0000-000000000001'
    begin
        set @RECORDTYPEIDNOTNULL = null;
    end
    else
    begin
        set @RECORDTYPEIDNOTNULL = @RECORDTYPEID;
    end;

    if @QUERYCATEGORYCODEID = '00000000-0000-0000-0000-000000000001'
    begin
        set @QUERYCATEGORYCODEIDNOTNULL = null;
    end
    else
    begin
        set @QUERYCATEGORYCODEIDNOTNULL = @QUERYCATEGORYCODEID;
    end;

    declare @ISSYSADMIN bit;

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

    /* Filter out any ad-hoc queries involving query views to which the current user does not have rights */
    if @ISSYSADMIN = 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 ADHOCQUERY.ID,
        ADHOCQUERY.NAME,
        ADHOCQUERY.DESCRIPTION,
        QUERYVIEWCATALOG.RECORDTYPEID,
        RECORDTYPE.NAME as [RECORDTYPE_NAME],
        ADHOCQUERY.QUERYCATEGORYCODEID,
        QUERYCATEGORYCODE.DESCRIPTION as [QUERYCATEGORYCODE_DESCRIPTION],
        APPUSER.USERNAME as [OWNER],
        ADHOCQUERY.OWNERID,
        ADHOCQUERY.OTHERSCANMODIFY,
        ADHOCQUERY.DATEADDED,
        CHANGEAGENT.USERNAME as [ADDEDBY_USERNAME]
    from dbo.ADHOCQUERY
    inner join dbo.QUERYVIEWCATALOG on ADHOCQUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
    left join dbo.RECORDTYPE on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
    left join dbo.APPUSER on ADHOCQUERY.OWNERID = dbo.APPUSER.ID
    left join dbo.QUERYCATEGORYCODE on ADHOCQUERY.QUERYCATEGORYCODEID = QUERYCATEGORYCODE.ID
    left join dbo.CHANGEAGENT on ADHOCQUERY.ADDEDBYID = CHANGEAGENT.ID
    where 
        /* Filter out restricted query views */
        (@ISSYSADMIN = 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.ADHOCQUERY as [ADHOCQUERYRIGHTSTEST]
                outer apply 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 */

        and (@RECORDTYPEID is null or ((@RECORDTYPEIDNOTNULL is null and QUERYVIEWCATALOG.RECORDTYPEID is null) or (QUERYVIEWCATALOG.RECORDTYPEID = @RECORDTYPEIDNOTNULL))) 
        and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and ADHOCQUERY.QUERYCATEGORYCODEID is null) or (ADHOCQUERY.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEIDNOTNULL)))
        and QUERYVIEWCATALOG.ROOTOBJECT = 1
    order by ADHOCQUERY.NAME;