USP_SEARCHLIST_SIGNATURE

Search for a signature.
Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(255)	IN	Name
@APPLICATIONCODE	tinyint	IN	Signature application
@APPLICATIONUSER	bit	IN	Application user
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
Definition

Copy


            CREATE procedure dbo.USP_SEARCHLIST_SIGNATURE
            (
                @NAME nvarchar(255) = null,
                @APPLICATIONCODE tinyint,
                @APPLICATIONUSER bit,
                @MAXROWS smallint = 500
            )
            as
                set @NAME = COALESCE(@NAME,'') + '%' ;

                iF @APPLICATIONUSER = 1 
                select top(@MAXROWS)
                    SIGNATURE.ID,
                    dbo.UFN_SIGNATURE_USERNAME(SIGNATURE.SIGNERCODE, SIGNATURE.NAME, SIGNATURE.APPUSERID) as NAME,                    
                    dbo.SIGNATUREAPPLICATIONTYPE.TYPE as APPLICATION,
                    (case SIGNERCODE when 0 then 'Yes' else 'No' end) as SIGNERCODEVALUE,
                    SIGNERCODE,
                    dbo.SIGNATUREAPPLICATIONTYPE.TYPECODE  as APPLICATIONCODE 
                from 
                    dbo.SIGNATURE
                    inner join dbo.SIGNATUREAPPLICATION on SIGNATUREAPPLICATION.SIGNATUREID = SIGNATURE.ID 
                    inner join dbo.SIGNATUREAPPLICATIONTYPE on SIGNATUREAPPLICATIONTYPE.ID = SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID
                where
                    (dbo.UFN_SIGNATURE_USERNAME(SIGNATURE.SIGNERCODE, SIGNATURE.NAME, SIGNATURE.APPUSERID) LIKE @NAME) 
                        and dbo.SIGNATUREAPPLICATIONTYPE.TYPECODE = @APPLICATIONCODE 
                        and (SIGNERCODE = 0)
                order by 
                    NAME asc;
                else
                select top(@MAXROWS)
                    SIGNATURE.ID,
                    dbo.UFN_SIGNATURE_USERNAME(SIGNATURE.SIGNERCODE, SIGNATURE.NAME, SIGNATURE.APPUSERID) as NAME,
                    dbo.SIGNATUREAPPLICATIONTYPE.TYPE as APPLICATION,                    
                    (case SIGNERCODE when 0 then 'Yes' else 'No' end) as SIGNERCODEVALUE,
                    SIGNERCODE, 
                    dbo.SIGNATUREAPPLICATIONTYPE.TYPECODE  as APPLICATIONCODE 
                from 
                    dbo.SIGNATURE
                    inner join dbo.SIGNATUREAPPLICATION on SIGNATUREAPPLICATION.SIGNATUREID = SIGNATURE.ID 
                    inner join dbo.SIGNATUREAPPLICATIONTYPE on SIGNATUREAPPLICATIONTYPE.ID = SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID
                where
                    (dbo.UFN_SIGNATURE_USERNAME(SIGNATURE.SIGNERCODE, SIGNATURE.NAME, SIGNATURE.APPUSERID) LIKE @NAME) 
                        and dbo.SIGNATUREAPPLICATIONTYPE.TYPECODE = @APPLICATIONCODE  
                        and (SIGNERCODE in (1,0))
                order by 
                    NAME asc;