USP_REPORT_ADDPROSPECTPLANSPROCESS

create procedure dbo.USP_REPORT_ADDPROSPECTPLANSPROCESS
(
    @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 APP.NAME PROCESSNAME,
                 IDSETREGISTER.NAME SELECTIONNAME,
                 BPS.STARTEDON,
                 NF.NAME CONSTITUENTNAME,
                 CONSTITUENT.LOOKUPID,
                 APPC.NEWPROSPECT,
                 APPC.RESULTCODE
          from dbo.ADDPROSPECTPLANSPROCESSSTATUS as APPS
          inner join dbo.ADDPROSPECTPLANSPROCESS 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.CONSTITUENT on CONSTITUENT.ID = APPC.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;