USP_MKTSEGMENTATIONACTIVATE_UPDATEAPPEALINFO

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_UPDATEAPPEALINFO]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONACTIVATEID uniqueidentifier;
  declare @VIEWNAME nvarchar(128);
  declare @PRIMARYKEYFIELD nvarchar(128);
  declare @APPEALIDFIELD nvarchar(128);
  declare @DESCRIPTIONFIELD nvarchar(128);
  declare @APPEALSYSTEMID nvarchar(36);
  declare @OLDAPPEALID nvarchar(100);
  declare @OLDAPPEALDESCRIPTION nvarchar(255);
  declare @NEWAPPEALID nvarchar(100);
  declare @NEWAPPEALDESCRIPTION nvarchar(255);
  declare @SQL nvarchar(max);

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

  declare RECORDSOURCECURSOR cursor local fast_forward for
    select
      [MKTSEGMENTATIONACTIVATE].[ID],
      [QUERYVIEWCATALOG].[OBJECTNAME],
      [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
      [MKTAPPEALRECORDSOURCE].[APPEALIDFIELD],
      [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD],
      [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],
      [MKTSEGMENTATIONACTIVATE].[APPEALID],
      [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    inner join dbo.[MKTAPPEALRECORDSOURCE] on [MKTAPPEALRECORDSOURCE].[ID] = [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTAPPEALRECORDSOURCE].[QUERYVIEWCATALOGID]
    where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID;

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @SEGMENTATIONACTIVATEID, @VIEWNAME, @PRIMARYKEYFIELD, @APPEALIDFIELD, @DESCRIPTIONFIELD, @APPEALSYSTEMID, @OLDAPPEALID, @OLDAPPEALDESCRIPTION;

  while (@@FETCH_STATUS = 0)
  begin
    --Grab the current appeal name and description from the record source...

    if len(isnull(@APPEALSYSTEMID, '')) >0
    begin
      set @SQL = 'select' + char(13) +
                 '  @NEWAPPEALID = [' + @APPEALIDFIELD + '],' + char(13) +
                 '  @NEWAPPEALDESCRIPTION = [' + @DESCRIPTIONFIELD + ']' + char(13) +
                 'from dbo.[' + @VIEWNAME + ']' + char(13) +
                 'where [' + @PRIMARYKEYFIELD + '] = @APPEALSYSTEMID';
      exec sp_executesql @SQL, N'@NEWAPPEALID nvarchar(100) output, @NEWAPPEALDESCRIPTION nvarchar(255) output, @APPEALSYSTEMID nvarchar(36)', @NEWAPPEALID = @NEWAPPEALID output, @NEWAPPEALDESCRIPTION = @NEWAPPEALDESCRIPTION output, @APPEALSYSTEMID = @APPEALSYSTEMID;
      --If the appeal name or description is different from what we have saved, then update ours...

      if @OLDAPPEALID <> @NEWAPPEALID or @OLDAPPEALDESCRIPTION <> @NEWAPPEALDESCRIPTION
        update dbo.[MKTSEGMENTATIONACTIVATE] set
          [APPEALID] = @NEWAPPEALID,
          [APPEALDESCRIPTION] = @NEWAPPEALDESCRIPTION,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [ID] = @SEGMENTATIONACTIVATEID;
    end
    else--we do not have a system ID yet so try to get it based on appeal ID

    begin
      set @SQL = 'select' + char(13) +
                 '  @APPEALSYSTEMID = cast([' + @PRIMARYKEYFIELD + '] as nvarchar(36)),' + char(13) +
                 '  @NEWAPPEALDESCRIPTION = [' + @DESCRIPTIONFIELD + ']' + char(13) +
                 'from dbo.[' + @VIEWNAME + ']' + char(13) +
                 'where [' + @APPEALIDFIELD + '] = @OLDAPPEALID';

      exec sp_executesql @SQL, N'@OLDAPPEALID nvarchar(100), @NEWAPPEALDESCRIPTION nvarchar(255) output, @APPEALSYSTEMID nvarchar(36) output', @OLDAPPEALID = @OLDAPPEALID, @NEWAPPEALDESCRIPTION = @NEWAPPEALDESCRIPTION output, @APPEALSYSTEMID = @APPEALSYSTEMID output;

      --If we found an appeal system ID then update ours

      if len(isnull(@APPEALSYSTEMID, '')) >0
        update dbo.[MKTSEGMENTATIONACTIVATE] set
          [APPEALSYSTEMID] = @APPEALSYSTEMID,
          [APPEALDESCRIPTION] = @NEWAPPEALDESCRIPTION,
      [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [APPEALID] = @OLDAPPEALID
        and [ID] = @SEGMENTATIONACTIVATEID;

    end

    fetch next from RECORDSOURCECURSOR into @SEGMENTATIONACTIVATEID, @VIEWNAME, @PRIMARYKEYFIELD, @APPEALIDFIELD, @DESCRIPTIONFIELD, @APPEALSYSTEMID, @OLDAPPEALID, @OLDAPPEALDESCRIPTION;
  end

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  return 0;