USP_CUSTOMFORMENTRY_BULKRESUBMIT

CREATE procedure dbo.USP_CUSTOMFORMENTRY_BULKRESUBMIT(@XML xml, @NUMSUCCESS int output, @NUMNOTCOMPLETED int output, @NUMWITHOUTTRANS int output)
AS    
BEGIN

  BEGIN TRAN
  set nocount on

  declare @CUSTOMFORMENTRYIDS table (ENTRYID int)
  insert into @CUSTOMFORMENTRYIDS select IDS.ID.value('.', 'int') from @XML.nodes('/CustomFormEntry/id') IDS(ID)

  declare @ENTRIESNOTCOMPLETED table (ENTRYID int)

  insert into @ENTRIESNOTCOMPLETED
  SELECT distinct cfe.ID
  FROM dbo.CUSTOMFORMENTRY cfe
  INNER JOIN @CUSTOMFORMENTRYIDS cfei ON cfe.ID = cfei.ENTRYID
  WHERE cfe.EntryState <> 2

  set @NUMNOTCOMPLETED = (select count(1) from @ENTRIESNOTCOMPLETED)

  delete @CUSTOMFORMENTRYIDS
  from @CUSTOMFORMENTRYIDS cfe
  inner join @ENTRIESNOTCOMPLETED enc on cfe.ENTRYID = enc.ENTRYID

  declare @ENTRYCUSTOMTRANSACTIONS table (ENTRYID int, CUSTOMTRANSACTIONID int)

  insert into @ENTRYCUSTOMTRANSACTIONS
  SELECT distinct cfe.ID, cfe.EntryData.value('/CustomFormEntry[1]/CustomTransactionID[1]/text()[1]', 'int')
  FROM dbo.CUSTOMFORMENTRY cfe
  INNER JOIN @CUSTOMFORMENTRYIDS cfei ON cfe.ID = cfei.ENTRYID

  set @NUMWITHOUTTRANS = (select count(1) from @ENTRYCUSTOMTRANSACTIONS where CUSTOMTRANSACTIONID = 0)

  delete @ENTRYCUSTOMTRANSACTIONS
  from @ENTRYCUSTOMTRANSACTIONS ect
  inner join @CUSTOMFORMENTRYIDS cfe on cfe.ENTRYID = ect.ENTRYID
  where ect.CUSTOMTRANSACTIONID = 0

  update CustomTransactions
  set DateProcessed=null
  from dbo.CustomTransactions
  inner join @ENTRYCUSTOMTRANSACTIONS ect on CustomTransactions.CustomTransactionID = ect.CUSTOMTRANSACTIONID

  set @NUMSUCCESS = (select count(1) from @ENTRYCUSTOMTRANSACTIONS);

  COMMIT TRAN    
END