USP_REVENUEATTRIBUTES_COPYTOREVENUE

CREATE procedure dbo.USP_REVENUEATTRIBUTES_COPYTOREVENUE
(
  @FROMREVENUEID uniqueidentifier,
  @TOREVENUEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime = getdate();

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare ATTRIBUTECURSOR cursor local fast_forward for
    select
      TABLECATALOG.TABLENAME as ATTRIBUTETABLENAME,
      ATTRIBUTECATEGORY.VALUECOLUMNNAME,
      ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD
    from
      dbo.ATTRIBUTECATEGORY
    inner join
      dbo.ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTECATEGORY.ATTRIBUTERECORDTYPEID
    inner join
      dbo.RECORDTYPE on RECORDTYPE.ID = ATTRIBUTERECORDTYPE.RECORDTYPEID
    inner join
      dbo.TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID 
    where
      RECORDTYPE.NAME = 'Revenue';

  declare @PARAMETERDEFINITION nvarchar(1000);
  declare @TABLENAME nvarchar(100);
  declare @VALUECOLUMN nvarchar(100);
  declare @ONLYALLOWONEPERRECORD bit;

  declare @IDCOLUMN nvarchar(50);

  open ATTRIBUTECURSOR;
  fetch next from ATTRIBUTECURSOR
    into @TABLENAME, @VALUECOLUMN, @ONLYALLOWONEPERRECORD;

  while @@FETCH_STATUS = 0
  begin
    declare @SQLQUERY nvarchar(2000) = '';

    if @ONLYALLOWONEPERRECORD = 0
    begin
      set @IDCOLUMN = 'REVENUEID';
    end
    else
    begin
      set @IDCOLUMN = 'ID';
      set @SQLQUERY = 'if not exists(select top 1 1 from dbo.' + @TABLENAME + ' where ID = @TOREVENUEID)';
    end

    set @SQLQUERY =  @SQLQUERY + 'insert into dbo.'+ @TABLENAME + '('+
      @IDCOLUMN+','+ 
      @VALUECOLUMN+','+ 
      'COMMENT,'+
      'STARTDATE,'+
      'ENDDATE,'+
      'ADDEDBYID,'+
      'CHANGEDBYID,'+
      'DATEADDED,'+
      'DATECHANGED) '+
      'select '+
      '@TOREVENUEID,'+
      @VALUECOLUMN+','+ 
      'COMMENT,'+
      'STARTDATE,'+
      'ENDDATE,'+
      '@CHANGEAGENTID,'+
      '@CHANGEAGENTID,'+
      '@CURRENTDATE,'+
      '@CURRENTDATE '+
      'from '+ 
      'dbo.'+@TABLENAME +
      ' where '+@IDCOLUMN+' = @FROMREVENUEID';

    set @PARAMETERDEFINITION = ' @FROMREVENUEID uniqueidentifier,
      @TOREVENUEID uniqueidentifier,
      @CHANGEAGENTID uniqueidentifier,
      @CURRENTDATE datetime';

    execute sp_executesql @SQLQUERY,
      @PARAMETERDEFINITION,
      @FROMREVENUEID,
      @TOREVENUEID,
      @CHANGEAGENTID,
      @CURRENTDATE

    fetch next from ATTRIBUTECURSOR
      into @TABLENAME, @VALUECOLUMN, @ONLYALLOWONEPERRECORD;
  end

  close ATTRIBUTECURSOR;
  deallocate ATTRIBUTECURSOR;