USP_REPORT_ADDSTEWARDSHIPPLANSTEPSPROCESS

create procedure dbo.USP_REPORT_ADDSTEWARDSHIPPLANSTEPSPROCESS
(
  @ID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(4000);
  declare @PARAMDEF nvarchar(25);

  select @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
  from dbo.BUSINESSPROCESSSTATUS 
  inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
  where BUSINESSPROCESSSTATUS.ID = @ID
  and BUSINESSPROCESSOUTPUT.TABLEKEY = 'Constituent';

  if @TABLENAME is null or OBJECT_ID(@TABLENAME) is null
    raiserror('Business process output table could not be found.  The process might not have completed successfully. ',13,1)

  set @SQL =
    N'select distinct APP.NAME PROCESSNAME,
         IDSETREGISTER.NAME SELECTIONNAME,
         BPS.STARTEDON,
         NF.NAME CONSTITUENTNAME,
         CONSTITUENT.LOOKUPID,
         STEWARDSHIPPLAN.NAME PLANNAME,
         APPC.RESULTCODE
      from dbo.ADDSTEWARDSHIPPLANSTEPSPROCESSSTATUS as APPS
      inner join dbo.ADDSTEWARDSHIPPLANSTEPSPROCESS as APP on APP.ID = APPS.PARAMETERSETID
      inner join dbo.IDSETREGISTER on IDSETREGISTER.ID = APP.IDSETREGISTERID
      inner join dbo.BUSINESSPROCESSSTATUS BPS on BPS.ID = APPS.ID
      cross join dbo.' + @TABLENAME + ' as APPC
      inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = APPC.PLANID
      inner join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
      where APPS.ID = @ID';

  set @PARAMDEF = '@ID uniqueidentifier';

  begin try
    exec sp_executesql @SQL, @PARAMDEF, @ID;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;