USP_REPORT_SPONSORSHIPRESERVEEXCEPTION

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

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

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

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

    begin try
        set @SQL = 'select ''http://www.blackbaud.com?SPOPPORTUNITYID='' + CONVERT(nvarchar(36),SPO.ID) SPOPPORTUNITYID,
               dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPO.ID) SPOPPORTUNITY, 
         SPO.LOOKUPID SPOPPORTUNITYLOOKUPID,
               case EXCEPTIONTAB.ERRORMESSAGE
               when ''BBERR_OPPORTUNITYNOTAVAILABLE'' then N''Opportunity is not available for reserve action.''                 
               end ERRORMESSAGE
        from dbo.' + @TABLENAME + ' EXCEPTIONTAB 
        inner join dbo.SPONSORSHIPOPPORTUNITY SPO on SPO.ID = EXCEPTIONTAB.SPOPPORTUNITYID
    union
    select '''' SPOPPORTUNITYID,
       '''' SPOPPORTUNITY,  
     '''' SPOPPORTUNITYLOOKUPID,
       ''* The maximum reservation number exceeded the available number of opportunities'' ERRORMESSAGE
       from dbo.BUSINESSPROCESSSTATUS BPS
       inner join dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS SPR on SPR.ID = BPS.BUSINESSPROCESSPARAMETERSETID
       where BPS.ID = ''' + convert(nvarchar(100),@ID) + ''' and NUMBEROFEXCEPTIONS + NUMBERPROCESSED <= SPR.MAXKEYNUMBER     
       order by ERRORMESSAGE desc';  

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

return 0;