USP_SEARCH_SMARTQUERYINSTANCE

CREATE procedure dbo.USP_SEARCH_SMARTQUERYINSTANCE
(
    @NAME nvarchar(255) = null, 
    @RECORDTYPEID uniqueidentifier = null, 
    @MAXROWS smallint = 500,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    set @NAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@NAME, 0, null);

    -- for backwards compatibility, if no @CURRENTAPPUSERID is given, then return all instances
    declare @ISSYSADMIN bit;
    if (@CURRENTAPPUSERID is null) or (@CURRENTAPPUSERID = '00000000-0000-0000-0000-000000000000')
        set @ISSYSADMIN = 1
    else
        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 top(@MAXROWS)
        SMARTQUERYINSTANCE.ID,
        SMARTQUERYINSTANCE.NAME,
        SMARTQUERYINSTANCE.DESCRIPTION,
        RECORDTYPE.NAME,
        SMARTQUERYINSTANCE.ID as [QUERYID],
        'Smart' as [QUERYTYPE],
        RECORDTYPE.ID as [RECORDTYPEID]
    from dbo.SMARTQUERYINSTANCE
    inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    left join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
    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)
        ) 
        and
        (@NAME is null or SMARTQUERYINSTANCE.NAME like @NAME) and (@RECORDTYPEID is null or @RECORDTYPEID = RECORDTYPE.ID);