USP_MKTSEGMENTLIST_DELETEIDSETS

create procedure dbo.[USP_MKTSEGMENTLIST_DELETEIDSETS]
(
  @SEGMENTLISTID uniqueidentifier,
  @STANDARDIDSETID uniqueidentifier = null,
  @DUPLICATEIDSETID uniqueidentifier = null,
  @IDSETRECORDTYPEID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;


    /*****************************************/
    /* Delete the standard IDSet and view... */
    /*****************************************/
    if @STANDARDIDSETID is not null
      begin
        declare @STANDARDVIEWNAME nvarchar(128);
        declare @SEGMENTSELECTIONID uniqueidentifier;
        declare @SEGMENTID uniqueidentifier;

        select
          @SEGMENTID = [SEGMENTID]
        from dbo.[MKTSEGMENTLIST]
        where [ID] = @SEGMENTLISTID;

        select
          @STANDARDVIEWNAME = [DBOBJECTNAME]
        from dbo.[IDSETREGISTER]
        where [ID] = @STANDARDIDSETID;

        select
          @SEGMENTSELECTIONID = [ID]
        from dbo.[MKTSEGMENTSELECTION]
        where [SEGMENTID] = @SEGMENTID;

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

        exec dbo.[USP_MKTSEGMENTSELECTION_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTSELECTIONID, @CHANGEAGENTID;

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

        exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @STANDARDIDSETID, @CHANGEAGENTID;
      end


    /******************************************/
    /* Delete the duplicate IDSet and view... */
    /******************************************/
    if @DUPLICATEIDSETID is not null
      begin
        declare @DUPLICATEVIEWNAME nvarchar(128);

        select
          @DUPLICATEVIEWNAME = [DBOBJECTNAME]
        from dbo.[IDSETREGISTER]
        where [ID] = @DUPLICATEIDSETID;

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

        exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @DUPLICATEIDSETID, @CHANGEAGENTID;
      end


    /**********************************/
    /* Delete the IDSet recordtype... */
    /**********************************/
    if @IDSETRECORDTYPEID is not null
      exec dbo.[USP_RECORDTYPE_DELETEBYID_WITHCHANGEAGENTID] @IDSETRECORDTYPEID, @CHANGEAGENTID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;