USP_MKTSEGMENT_DELETEVIEW

CREATE procedure dbo.[USP_MKTSEGMENT_DELETEVIEW]
(
  @SEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @NAME nvarchar(255);
  declare @IDSETREGISTERID uniqueidentifier;

  select
    @NAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([ID]),
    @IDSETREGISTERID = [IDSETREGISTERID]
  from dbo.[MKTSEGMENT]
  where [ID] = @SEGMENTID;

  update dbo.[MKTSEGMENT] set
    [IDSETREGISTERID] = null,
    [CHANGEDBYID] = @CHANGEAGENTID,
    [DATECHANGED] = getdate()
  where [ID] = @SEGMENTID;

  -- Check to see if the selection is being used in a merged selection

  if not exists (select ID from dbo.IDSETREGISTERMERGE where IDSETREGISTERID = @IDSETREGISTERID or SELECTION1ID = @IDSETREGISTERID or SELECTION2ID = @IDSETREGISTERID)
  begin
      --Remove the VIEW from the IDSETREGISTER

      exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;

      --Drop the segment view...

      if exists(select 1 from [INFORMATION_SCHEMA].[VIEWS] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @NAME)
        exec ('drop view dbo.[' + @NAME + ']');
  end

  return 0;