USP_MKTCOMMUNICATIONNAMESCHEME_SEARCH

CREATE procedure dbo.[USP_MKTCOMMUNICATIONNAMESCHEME_SEARCH]
(
  @MAXROWS smallint,
  @NAME nvarchar(100) = null,
  @SITEID uniqueidentifier = null,
  @STATUS tinyint = null
)
as
  set nocount on;

  select
    [MKTCOMMUNICATIONNAMESCHEME].[ID],
    [MKTCOMMUNICATIONNAMESCHEME].[NAME],
    dbo.[UFN_MKTCOMMUNICATIONNAMESCHEME_GETCURRENTNAMESCHEMETEXT]([MKTCOMMUNICATIONNAMESCHEME].[ID]) as [FORMAT],
    [SITE].[NAME] as [SITE]
  from dbo.[MKTCOMMUNICATIONNAMESCHEME]
  left join dbo.[SITE] on [SITE].[ID] = [MKTCOMMUNICATIONNAMESCHEME].[SITEID]
  where 
    (@NAME is null or [MKTCOMMUNICATIONNAMESCHEME].[NAME] like @NAME + '%')
    and (@SITEID is null or [SITE].[ID] = @SITEID)
    and (@STATUS is null or (@STATUS = [MKTCOMMUNICATIONNAMESCHEME].[ISACTIVE]))
    and [MKTCOMMUNICATIONNAMESCHEME].[PARENTCOMMUNICATIONNAMESCHEMEID] is null and [MKTCOMMUNICATIONNAMESCHEME].[MKTSEGMENTATIONID] is null
  order by [MKTCOMMUNICATIONNAMESCHEME].[NAME];

  return 0;