UFN_MKTSEGMENT_CODEISUNIQUE

CREATE function dbo.[UFN_MKTSEGMENT_CODEISUNIQUE]
(
  @SEGMENTTYPECODE tinyint,
  @ID uniqueidentifier,
  @CODE nvarchar(10) = null,
  @LISTID uniqueidentifier = null
)
returns bit
as
begin
  declare @UNIQUE bit;

  if @SEGMENTTYPECODE = 1 -- constituent

    begin
      -- a constituent segment's code must either be blank or be unique amongst all constituent and list segments

      if isnull(@CODE, '') = ''
        set @UNIQUE = 1;
      else
        set @UNIQUE = (case when exists(select top 1 1 from dbo.[MKTSEGMENT] where [CODE] = @CODE and (@ID is null or [ID] <> @ID) and [SEGMENTTYPECODE] in (1, 2))
                       then 0 else 1 end);
    end
  else if @SEGMENTTYPECODE = 2 -- list

    begin
      --A list segment's code is required and must be not be in use by a constituent segment 

      --and must be unique amongst all segments with the same parent list.

      --The problem here is that CODE is in MKTSEGMENT while LISTID is in MKTSEGMENTLIST.

      --This will need to be a constraint on both tables:

      --  If called from MKTSEGMENT, @CODE will have a value

      --  If called from MKTSEGMENTLIST, @LISTID will have a value


      if @CODE is null
        select @CODE = [CODE]
        from dbo.[MKTSEGMENT]
        where [ID] = @ID;

      if @LISTID is null
        select @LISTID = [MKTSEGMENTLIST].[LISTID]
        from dbo.[MKTSEGMENT]
        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
        where [MKTSEGMENT].[ID] = @ID;

      if @CODE is not null and @LISTID is not null
        begin
          --Check against all constituent segments...

          set @UNIQUE = (case when exists(select top 1 1 from dbo.[MKTSEGMENT] where [SEGMENTTYPECODE] = 1 and [CODE] = @CODE and (@ID is null or [ID] <> @ID))
                         then 0 else 1 end);

          --Check against all list segments from the same parent list...

          if @UNIQUE = 1
            set @UNIQUE = (case when exists(select top 1 1 from dbo.[MKTSEGMENT] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID] where [MKTSEGMENTLIST].[LISTID] = @LISTID and [MKTSEGMENT].[CODE] = @CODE and (@ID is null or [MKTSEGMENT].[ID] <> @ID))
                           then 0 else 1 end);
        end
      else
        set @UNIQUE = 1; -- and the other table will check it

    end
  else if @SEGMENTTYPECODE in (6, 7, 8)  --Public media

    begin
      --A public media's code must either be blank or be unique among all public media

      if isnull(@CODE, '') = ''
        set @UNIQUE = 1;
      else
        set @UNIQUE = (case when exists(select top 1 1 from dbo.[MKTSEGMENT] where [CODE] = @CODE and (@ID is null or [ID] <> @ID) and [SEGMENTTYPECODE] in (6, 7, 8))
                       then 0 else 1 end);
    end
  else -- all other segment types

    begin
      -- the segment's code must either be blank or be unique among all segments of the same type

      if isnull(@CODE, '') = ''
        set @UNIQUE = 1;
      else
        set @UNIQUE = (case when exists(select top 1 1 from dbo.[MKTSEGMENT] where [CODE] = @CODE and (@ID is null or [ID] <> @ID) and [SEGMENTTYPECODE] = @SEGMENTTYPECODE)
                       then 0 else 1 end);
    end

  return @UNIQUE;
end