USP_MKTSOURCEANALYSISRULEFIELDS_REMOVETABLECOLUMNS

CREATE procedure dbo.[USP_MKTSOURCEANALYSISRULEFIELDS_REMOVETABLECOLUMNS]
(
  @RECORDSOURCEID uniqueidentifier,
  @SOURCEANALYSISRULEID uniqueidentifier,
  @VALIDFIELDS xml
)
with execute as owner
as
  set nocount on;

  begin try

    declare @SQL nvarchar(max);
    declare @DATATABLE nvarchar(128) = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
    declare @CACHETABLECOLUMNNAME nvarchar(255);

    /* Loop through each of the deleted SAR fields and remove the column names from the SAR table */
    declare field_cursor cursor local fast_forward for
      select [CACHETABLECOLUMNNAME]
      from dbo.[MKTSOURCEANALYSISRULEFIELDS]
      where [MKTSOURCEANALYSISRULEFIELDS].[SOURCEANALYSISRULEID] = @SOURCEANALYSISRULEID
            and not exists (select 1
                            from @VALIDFIELDS.nodes('//FIELDLIST/ITEM') as [VALIDFIELDS](Item)
                            where [VALIDFIELDS].Item.value('ID[1]', 'uniqueidentifier') = [MKTSOURCEANALYSISRULEFIELDS].[ID]);

    open field_cursor;
    fetch next from field_cursor into @CACHETABLECOLUMNNAME;

    while @@FETCH_STATUS = 0
    begin
      set @SQL = 'alter table dbo.[' + @DATATABLE + '] drop column [' + @CACHETABLECOLUMNNAME + '];';
      exec (@SQL);

      fetch next from field_cursor into @CACHETABLECOLUMNNAME;
    end

    close field_cursor;
    deallocate field_cursor;

  end try

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

  return 0;