USP_ADDBATCHWORKFLOWTASK_ADDENTRY

CREATE procedure dbo.USP_ADDBATCHWORKFLOWTASK_ADDENTRY(
  @XML xml
  , @CHANGEAGENTID uniqueidentifier = NULL
  , @WORKFLOWID uniqueidentifier = NULL
)
as
begin
  declare @CHANGEDATE datetime;

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

set @CHANGEDATE = getdate(); 

if exists (select COUNT(NAME) from dbo.UFN_BATCHWORKFLOWTASK_FROMITEMLISTXML(@XML) group by NAME having COUNT(NAME)>1)
raiserror('ERR_TASKNAME_DUPLICATE',13,1);

if (select COUNT(NAME) from dbo.UFN_BATCHWORKFLOWTASK_FROMITEMLISTXML(@XML) WHERE BATCHWORKFLOWSTATEID = NEXTBATCHWORKFLOWSTATEID) > 0
raiserror('ERR_WORKFLOWTASK_SAMETOFROM',13,1);



merge dbo.BATCHWORKFLOWTASK as T
using dbo.UFN_BATCHWORKFLOWTASK_FROMITEMLISTXML(@XML) S
on (T.ID = S.ID)
when not matched by target then
  insert (
    ID
    , BATCHWORKFLOWSTATEID
    , NAME
    , NEXTBATCHWORKFLOWSTATEID
    , BATCHWORKFLOWID
    , ADDEDBYID
    , CHANGEDBYID
    , DATEADDED
    , DATECHANGED
  )
  values (
    newid()
    , S.BATCHWORKFLOWSTATEID
    , S.NAME
    , S.NEXTBATCHWORKFLOWSTATEID
    , @WORKFLOWID
    , @CHANGEAGENTID
    , @CHANGEAGENTID
    , @CHANGEDATE
    , @CHANGEDATE
  )
when matched then
  update set 
    T.BATCHWORKFLOWSTATEID = S.BATCHWORKFLOWSTATEID
    , T.NAME = S.NAME
    , T.NEXTBATCHWORKFLOWSTATEID = S.NEXTBATCHWORKFLOWSTATEID
    , T.CHANGEDBYID = @CHANGEAGENTID
    , T.DATECHANGED = @CHANGEDATE
when not matched by source and T.BATCHWORKFLOWID= @WORKFLOWID then delete ;

end;