USP_SEARCHLIST_SMARTQUERY

Search for a smart query.

Parameters

Parameter	Parameter Type	Mode	Description
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@NAME	nvarchar(60)	IN	Name
@RECORDTYPEID	uniqueidentifier	IN	Record type

Definition

Copy



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

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

    select top(@MAXROWS)
        SMARTQUERYCATALOG.ID,
        SMARTQUERYCATALOG.UINAME as NAME,
        SMARTQUERYCATALOG.DESCRIPTION,
        RECORDTYPE.NAME as RECORDTYPENAME
    from 
        dbo.SMARTQUERYCATALOG 
        inner join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
    where ((@NAME is null) or (SMARTQUERYCATALOG.UINAME like @NAME)) and
          ((@RECORDTYPEID is null) or (SMARTQUERYCATALOG.RECORDTYPEID = @RECORDTYPEID))
    order by SMARTQUERYCATALOG.UINAME asc;