USP_SEARCHLIST_CODETABLE

Search for a code table.

Parameters

Parameter	Parameter Type	Mode	Description
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@NAME	nvarchar(100)	IN	Name
@DBTABLENAME	nvarchar(100)	IN	Database table name

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_CODETABLE
(
    @MAXROWS smallint = 500,
    @NAME nvarchar(100) = null,
    @DBTABLENAME nvarchar(100) = null
)
as
    set nocount on;

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

    with xmlnamespaces ('bb_appfx_codetable' as tns)
    select top(@MAXROWS)
        CODETABLECATALOG.ID,
        CODETABLECATALOG.UINAME as NAME,
        CODETABLECATALOG.CODETABLESPECXML.value('(tns:CodeTableSpec/@Description)[1]', 'nvarchar(1000)') as DESCRIPTION,
        CODETABLECATALOG.DBTABLENAME as DBTABLENAME
    from 
        dbo.CODETABLECATALOG
    where ((@NAME is null) or (CODETABLECATALOG.UINAME like @NAME)) and
          ((@DBTABLENAME is null) or (CODETABLECATALOG.DBTABLENAME like @DBTABLENAME))
    order by CODETABLECATALOG.UINAME asc;