USP_EXPORTDEFINITION_DELETE

CREATE procedure dbo.USP_EXPORTDEFINITION_DELETE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as begin
  -- check deletion rules, if any

  declare @VIEWDATAFORMTEMPLATEID uniqueidentifier;
  declare @VIEWDATAFORMINSTANCEID uniqueidentifier;
  declare @LOADSPECLOGID uniqueidentifier;
  declare @MKTEXPORTDEFINITIONID uniqueidentifier;
  declare @SQL nvarchar(max);

  select @VIEWDATAFORMTEMPLATEID = [VIEWDATAFORMTEMPLATEID]
  from dbo.[EXPORTDEFINITION]
  where [ID] = @ID;

  if exists (select top 1 1 from dbo.sysobjects where name = 'MKTEXPORTDEFINITION') -- may not exist, as in a BBPay build

    begin
      -- EXPORTDEFINITIONID is being added to MKTEXPORTDEFINITION in a Galileo revision, so...

      set @SQL = 'select @MKTEXPORTDEFINITIONID = [ID] from dbo.[MKTEXPORTDEFINITION] where [EXPORTDEFINITIONID] = @ID';

      exec sp_executesql @SQL, N'@MKTEXPORTDEFINITIONID uniqueidentifier output, @ID uniqueidentifier', @MKTEXPORTDEFINITIONID = @MKTEXPORTDEFINITIONID output, @ID = @ID;
    end

  if not @MKTEXPORTDEFINITIONID is null
    begin
      if exists (select top 1 1 from dbo.[MKTEXPORTDEFINITION] where [ID] = @MKTEXPORTDEFINITIONID and [NETCOMMUNITYDATASOURCEID] > 0)
        if exists (select top 1 1 from dbo.[EmailTemplate] where [Deleted] = 0 and [DataSourceID] = (select [NETCOMMUNITYDATASOURCEID] from dbo.[MKTEXPORTDEFINITION] where [ID] = @MKTEXPORTDEFINITIONID))
          begin
            raiserror ('ERR_EXPORTDEFINITION_INUSEBYNETCOMMUNITYEMAILTEMPLATE', 13, 1);
            return 1;
          end

      exec dbo.[USP_MKTEXPORTDEFINITION_DELETE] @MKTEXPORTDEFINITIONID, @CHANGEAGENTID;
    end

  -- use the system generated delete routine to allow proper recording of the deleting agent

  exec USP_EXPORTDEFINITION_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

  if @VIEWDATAFORMTEMPLATEID is not null
    begin
      select @VIEWDATAFORMINSTANCEID = [ID]
      from dbo.[DATAFORMINSTANCECATALOG]
      where [DATAFORMTEMPLATECATALOGID] = @VIEWDATAFORMTEMPLATEID;

      exec dbo.[USP_DATAFORMINSTANCECATALOG_DELETEBYID_WITHCHANGEAGENTID] @VIEWDATAFORMINSTANCEID, @CHANGEAGENTID;
      exec dbo.[USP_DATAFORMTEMPLATECATALOG_DELETEBYID_WITHCHANGEAGENTID] @VIEWDATAFORMTEMPLATEID, @CHANGEAGENTID;

      declare LOADSPECLOGCURSOR cursor local fast_forward for
        select [ID]
        from dbo.[LOADSPECLOG]
        where [SPECID] = @VIEWDATAFORMTEMPLATEID;

      open LOADSPECLOGCURSOR;
      fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;

      while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;
          fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;
        end

      close LOADSPECLOGCURSOR;
      deallocate LOADSPECLOGCURSOR;
    end

  return 0;
end