USP_APPEALMAILING_COPYLABELFILE

create procedure dbo.[USP_APPEALMAILING_COPYLABELFILE]
(
  @NEWAPPEALMAILINGID uniqueidentifier,
  @OLDAPPEALMAILINGID uniqueidentifier=null,
  @CHANGEAGENTID uniqueidentifier = null,
  @DISPLAYEDFILENAME nvarchar(255) = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @LABELFILENAME nvarchar(255);


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

    set @CURRENTDATE = getdate();

    --Grab some info so we know whether to add or update (and what to update)...

    if exists (select 1 from dbo.[APPEALMAILING] where [LABELFILE] is not null and [ID] = @OLDAPPEALMAILINGID)
    begin
      declare @LABELFILE varbinary(max);
      select @LABELFILE = [LABELFILE], @LABELFILENAME = case when @DISPLAYEDFILENAME is null then [LABELFILENAME] else @DISPLAYEDFILENAME end from dbo.[APPEALMAILING] where [LABELFILE] is not null and [ID] = @OLDAPPEALMAILINGID;

      update dbo.[APPEALMAILING]
      set
        [LABELFILE] = @LABELFILE,
        [LABELFILENAME] = @LABELFILENAME,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where
        [LABELFILE] is null 
        and 
        [ID] = @NEWAPPEALMAILINGID;
    end

    if @OLDAPPEALMAILINGID is null and (@DISPLAYEDFILENAME is not null and @NEWAPPEALMAILINGID is not null)
    begin
      update dbo.[APPEALMAILING]
      set
        [LABELFILENAME] = @DISPLAYEDFILENAME,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where
        [ID] = @NEWAPPEALMAILINGID;
    end
  end try

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

  return 0;