USP_MARKETINGDOCUMENTSEARCH

Search for Marketing Documents

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@SITEID	uniqueidentifier	IN	Site
@SITEIDISNULL	bit	IN	Site ID is null?
@DOCUMENTTYPECODEID	uniqueidentifier	IN	Type
@DOCUMENT	nvarchar(255)	IN	Document
@EXACTMATCHONLY	bit	IN	Match all criteria exactly
@BASECURRENCYID	uniqueidentifier	IN	Base currency ID

Definition

Copy



CREATE procedure dbo.[USP_MARKETINGDOCUMENTSEARCH]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @MAXROWS smallint,
  @NAME nvarchar(100) = null,
  @DESCRIPTION nvarchar(255) = null,
  @SITEID uniqueidentifier = null,
  @SITEIDISNULL bit = 0,
  @DOCUMENTTYPECODEID uniqueidentifier = null,
  @DOCUMENT nvarchar(255) = null,
  @EXACTMATCHONLY bit = 0,
  @BASECURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  select top (@MAXROWS)
    [MKTDOCUMENT].[ID],
    [MKTDOCUMENT].[NAME],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTDOCUMENT].[SITEID]) as [SITE],
    [MKTDOCUMENT].[COST],
    [MKTDOCUMENT].[COSTDISTRIBUTIONMETHOD],
    [MKTDOCUMENTTYPECODE].[DESCRIPTION] as [DOCUMENTTYPE],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then [MKTDOCUMENT].[FILENAME] else [MKTDOCUMENT].[FILEURL] end as [DOCUMENT],
    [MKTDOCUMENT].[DESCRIPTION],
    [MKTDOCUMENT].[BASECURRENCYID]
  from dbo.[MKTDOCUMENT]
  left outer join dbo.[MKTDOCUMENTTYPECODE] on [MKTDOCUMENTTYPECODE].[ID] = [MKTDOCUMENT].[DOCUMENTTYPECODEID]
  where (@NAME is null or [MKTDOCUMENT].[NAME] like @NAME + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and (@SITEID is null or [MKTDOCUMENT].[SITEID] = @SITEID)
    and (@SITEIDISNULL = 0 or (@SITEIDISNULL = 1 and [MKTDOCUMENT].[SITEID] is null))
    and (@DOCUMENTTYPECODEID is null or [MKTDOCUMENT].[DOCUMENTTYPECODEID] = @DOCUMENTTYPECODEID)
    and (@DOCUMENT is null or case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then [MKTDOCUMENT].[FILENAME] else [MKTDOCUMENT].[FILEURL] end like @DOCUMENT + case when @EXACTMATCHONLY = 0 then '%' else '' end)
    and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTDOCUMENT].[SITEID]) = 1
    and (@BASECURRENCYID is null or [MKTDOCUMENT].[BASECURRENCYID] = @BASECURRENCYID)
  order by [MKTDOCUMENT].[NAME];

  return 0;