USP_ADDSTEWARDSHIPPLANSPROCESS_DELETEPLANS

CREATE procedure dbo.USP_ADDSTEWARDSHIPPLANSPROCESS_DELETEPLANS
( 
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  begin try

    --Validate that the process is not running before deleting plans...

    declare @STATUS tinyint;
    select 
      @STATUS = STATUSCODE 
    from BUSINESSPROCESSSTATUS
    where ID = @ID;

    if @STATUS = 1
      raiserror('The business process is currently running. The associated plans cannot be deleted.', 13, 1);

    declare @contextCache varbinary(128);

    /* cache current context information */
    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
      set CONTEXT_INFO @CHANGEAGENTID

    declare @TABLENAME nvarchar(60);
    set @TABLENAME = 'dbo.ADDSTEWARDSHIPPLANS_' + replace(convert(nvarchar(36), @ID), '-', '_');

    declare @SQL nvarchar(250);
    set @SQL = 'delete dbo.STEWARDSHIPPLAN from dbo.STEWARDSHIPPLAN ' +
               'inner join ' + @TABLENAME + ' PLANS on STEWARDSHIPPLAN.ID = PLANS.PLANID';

    exec sp_executesql @SQL

    --Set the status message to 'Results cleared' for this process status...

    update dbo.[BUSINESSPROCESSSTATUS] set 
      [STATUSCODE] = 3,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [ID] = @ID;

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
      set CONTEXT_INFO @contextCache

    return 0;
  end try

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