USP_REPORT_GENERATECONSTITUENTUPDATEBATCH_EXCEPTION

create procedure dbo.USP_REPORT_GENERATECONSTITUENTUPDATEBATCH_EXCEPTION
(
    @ID nvarchar(36)
)
with execute as owner
as
set nocount on;

declare @TABLENAME nvarchar(255);
declare @SQL nvarchar(4000);

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

    if not @TABLENAME is null and not OBJECT_ID(@TABLENAME) is null
        set @SQL = '
            select 
                T.REASON, 
                T.RECORDID, 
                CONSTITUENT.NAME,                 
                ''http://www.blackbaud.com/CONSTITUENTID?ID='' + convert(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID                
            from ' + @TABLENAME + ' as T             
            left join dbo.CONSTITUENT on T.RECORDID = CONSTITUENT.ID'

    else
        set @SQL = '
            select
                ID as [REASON], 
                null as RECORDID, 
                null as NAME                
            from BUSINESSPROCESSSTATUS 
            where 1 = 0';

    exec sp_executesql @SQL;
end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch
return 0;