USP_DATALIST_APPUSERWORKSPACESMARTQUERYINSTANCE

CREATE procedure dbo.USP_DATALIST_APPUSERWORKSPACESMARTQUERYINSTANCE
(
    @CURRENTAPPUSERID uniqueidentifier
)
as

    set nocount on;

    declare @ISSYSADMIN bit;

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

    /*Filter out smart query instances involving smart queries to which the user does not have access*/
    if @ISSYSADMIN = 0
    begin
        declare @OKSMARTQUERIES table (SMARTQUERYCATALOGID uniqueidentifier);

        insert into @OKSMARTQUERIES (SMARTQUERYCATALOGID)
        select SMARTQUERYCATALOG.ID from dbo.SMARTQUERYCATALOG
        where exists (
            select [OKSMARTQUERIES].SMARTQUERYCATALOGID
            from dbo.UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER(@CURRENTAPPUSERID) as [OKSMARTQUERIES] where [OKSMARTQUERIES].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID);
    end;

    select
        SMARTQUERYINSTANCE.ID,
        SMARTQUERYINSTANCE.NAME,
        SMARTQUERYINSTANCE.DESCRIPTION,
        RECORDTYPE.NAME [TYPE],
        QUERYCATEGORYCODE.DESCRIPTION [CATEGORY],
        SMARTQUERYCATALOG.NAME [SOURCE]
    from
        dbo.SMARTQUERYINSTANCE
    inner join
        dbo.SMARTQUERYCATALOG on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
    inner join
        dbo.RECORDTYPE on RECORDTYPE.ID = SMARTQUERYCATALOG.RECORDTYPEID
    left join
        dbo.QUERYCATEGORYCODE on QUERYCATEGORYCODE.ID = SMARTQUERYINSTANCE.QUERYCATEGORYCODEID
    where
        /* Filter out restricted smart queries */
        (@ISSYSADMIN = 1
            /*This will return a list of smart queries in use by the smart query but not in the OK smart queries table variable;
            These smart queries should not be returned to the client.*/
            or 
            exists (select [OKSMARTQUERIES].SMARTQUERYCATALOGID 
                    from @OKSMARTQUERIES as [OKSMARTQUERIES] 
                    where [OKSMARTQUERIES].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID)
            and
            (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, SMARTQUERYINSTANCE.ID) = 1)
        )

    return 0;