USP_SEARCHLIST_QUERY

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

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

    select top(@MAXROWS)
        ADHOCQUERY.ID as [ID],                    
        ADHOCQUERY.NAME as [Name], 
        (select RECORDTYPE.NAME from dbo.RECORDTYPE where ID = QVC.RECORDTYPEID) as [Type],
        (select DESCRIPTION from dbo.QUERYCATEGORYCODE where ID = dbo.ADHOCQUERY.QUERYCATEGORYCODEID) as [Category],
        ADHOCQUERY.DATEADDED as [Date added],
        ADHOCQUERY.DESCRIPTION as [Description],                     
        ADDEDBY.USERNAME as [Added by user],
        ADDEDBY.APPLICATIONNAME as [Added by application]
    from dbo.ADHOCQUERY
        inner join dbo.QUERYVIEWCATALOG as QVC on ADHOCQUERY.QUERYVIEWCATALOGID = QVC.ID
        inner join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = ADHOCQUERY.ADDEDBYID
        inner join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = ADHOCQUERY.CHANGEDBYID
    where
         ((@NAME is null) or (ADHOCQUERY.NAME like @NAME)) and
         ((@QUERYCATEGORYCODEID is null) or (ADHOCQUERY.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEID))
    order by ADHOCQUERY.NAME asc

    --Need optimizer hint so it knows to use the proper index
    OPTION (OPTIMIZE FOR (@NAME = 'Sample query name',@QUERYCATEGORYCODEID=null));