USP_SIGNATUREAPPLICATION_UPDATEFROMXML

create procedure dbo.USP_SIGNATUREAPPLICATION_UPDATEFROMXML(
@SIGNATUREID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
  set nocount on;

  if @CHANGEAGENTID is null
      exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  if @CHANGEDATE is null
    set @CHANGEDATE = getdate();

  declare @TempTbl table(
    [ID] uniqueidentifier,
    [SIGNATUREAPPLICATIONTYPEID] uniqueidentifier)

  insert into @TempTbl select
    [ID],
    [SIGNATUREAPPLICATIONTYPEID]
  from dbo.UFN_SIGNATUREAPPLICATION_FROMITEMLISTXML(@XML)

  update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

  if @@Error <> 0
    return 1;

  declare @contextCache varbinary(128);
  declare @e int;

  -- cache current context information

  set @contextCache = CONTEXT_INFO();

  -- set CONTEXT_INFO to @CHANGEAGENTID

  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  delete from dbo.SIGNATUREAPPLICATION where SIGNATUREAPPLICATION.ID in
    (select ID from dbo.SIGNATUREAPPLICATION where SIGNATUREAPPLICATION.SIGNATUREID = @SIGNATUREID
      EXCEPT select ID from @TempTbl)

  select @e = @@error;

  -- reset CONTEXT_INFO to previous value 

  if not @contextCache is null
      set CONTEXT_INFO @contextCache;

  if @e <> 0
      return 2;

  update SIGNATUREAPPLICATION set
    SIGNATUREAPPLICATION.ID = temp.ID,
    SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID = temp.SIGNATUREAPPLICATIONTYPEID,
    SIGNATUREAPPLICATION.CHANGEDBYID = @CHANGEAGENTID,
    SIGNATUREAPPLICATION.DATECHANGED = @CHANGEDATE
  from DBO.SIGNATUREAPPLICATION
  inner join @TempTbl as temp on SIGNATUREAPPLICATION.ID = temp.ID
  where (SIGNATUREAPPLICATION.ID <> temp.ID) or
        (SIGNATUREAPPLICATION.ID is null and temp.ID is not null) or
        (SIGNATUREAPPLICATION.ID is not null and temp.ID is null) or
        (SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID <> temp.SIGNATUREAPPLICATIONTYPEID) or
        (SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID is null and temp.SIGNATUREAPPLICATIONTYPEID is not null) or
        (SIGNATUREAPPLICATION.SIGNATUREAPPLICATIONTYPEID is not null and temp.SIGNATUREAPPLICATIONTYPEID is null)

  if @@Error <> 0
    return 3;

  insert into SIGNATUREAPPLICATION(
    ID,
    SIGNATUREID,
    SIGNATUREAPPLICATIONTYPEID,
    ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED)
  select 
    ID,
    @SIGNATUREID,
    SIGNATUREAPPLICATIONTYPEID,
    @CHANGEAGENTID, 
      @CHANGEAGENTID, 
      @CHANGEDATE, 
      @CHANGEDATE
  from @TempTbl as temp
  where not exists (select ID from dbo.SIGNATUREAPPLICATION as data where data.ID = temp.ID)

  if @@Error <> 0
    return 4;

  return 0;