USP_REPORT_ADDOPPORTUNITIESPROCESS

create procedure dbo.USP_REPORT_ADDOPPORTUNITIESPROCESS
(
    @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;

    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 [ADDOPPPROC].NAME PROCESSNAME,
                 IDSETREGISTER.NAME SELECTIONNAME,
                 BPS.STARTEDON,
                 NF.NAME CONSTITUENTNAME,
                 CONSTITUENT.LOOKUPID,
                 PROSPECTPLAN.NAME PLANNAME,
                 [RESULT].RESULTCODE
          from dbo.ADDOPPORTUNITIESPROCESSSTATUS as [STATUS]
          inner join dbo.ADDOPPORTUNITIESPROCESS as [ADDOPPPROC] on [ADDOPPPROC].ID = [STATUS].PARAMETERSETID
            inner join dbo.IDSETREGISTER on IDSETREGISTER.ID = [ADDOPPPROC].IDSETREGISTERID
          inner join dbo.BUSINESSPROCESSSTATUS BPS on BPS.ID = [STATUS].ID
          cross join dbo.' + @TABLENAME + ' as [RESULT]
          inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = [RESULT].PROSPECTPLANID
          inner join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLAN.PROSPECTID
          outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
          where [STATUS].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;