UFN_MKTSEGMENTATION_NAMEEXISTS

create function dbo.[UFN_MKTSEGMENTATION_NAMEEXISTS](
  @NAME nvarchar(100),
  @MAILINGTYPECODE tinyint,
  @COMMUNICATIONTYPECODE tinyint
)
returns bit
with execute as caller
as begin

  -- Appeal mailing name must be unique for each appeal.

  if @COMMUNICATIONTYPECODE = 1 
  begin
    if exists 
         (select 1
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
          where [MKTSEGMENTATION].[NAME] = @NAME and [MKTSEGMENTATION].[MAILINGTYPECODE] = @MAILINGTYPECODE
          group by [APPEALMAILING].[APPEALID]
          having count(*) > 1)
    begin
      -- Appeal name not unique.

      return 1
    end
  end
  else
  begin
    -- Check for uniqueness of non-appeal type marketing effort name.

    if exists 
         (select 1
          from dbo.[MKTSEGMENTATION] 
          where [NAME] = @NAME and [COMMUNICATIONTYPECODE] = @COMMUNICATIONTYPECODE and [MAILINGTYPECODE] = @MAILINGTYPECODE
          having count(*) > 1)
    begin
      -- Name not unique.

      return 1
    end
  end

  return 0
end