USP_REPORT_SPONSORSHIPTERMINATEREPORT

create procedure dbo.USP_REPORT_SPONSORSHIPTERMINATEREPORT
(
    @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 = 'Success';

    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/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) CONSTITUENTID,
               NF.NAME SPONSORNAME,
               ''http://www.blackbaud.com/TERMINATESPONSORSHIPID?TERMINATESPONSORSHIPID='' + CONVERT(nvarchar(36),SPONSORSHIP.ID) TERMINATESPONSORSHIPID,
               dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) OPPORTUNITY,
               case EXCEPTIONTAB.ERRORMESSAGE
               when ''BBERR_PENDINGSPONSORSHIPTERMINATE'' then N''Sponsorship pending - cannot be terminated.''
               when ''BBERR_INACTIVESPONSORSHIPTERMINATE'' then N''Sponsorship inactive - cannot be terminated.''        
               when ''SUCCESS'' then N''''
         else N''Unexpected exception: ''+EXCEPTIONTAB.ERRORMESSAGE
               end ERRORMESSAGE,
               SPONSORSHIPOPPORTUNITY.LOOKUPID,
               CONSTITUENT.LOOKUPID as SPONSORLOOKUPID
        from dbo.' + @TABLENAME + ' EXCEPTIONTAB 
        inner join dbo.SPONSORSHIP SPONSORSHIP on SPONSORSHIP.ID = EXCEPTIONTAB.TERMINATESPONSORSHIPID
        inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIP.SPONSORSHIPOPPORTUNITYID =SPONSORSHIPOPPORTUNITY.ID
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIP.CONSTITUENTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF';

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

return 0;