USP_REPORT_SPONSORSHIPTRANSFER

create procedure dbo.USP_REPORT_SPONSORSHIPTRANSFER
(
    @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,
               CONSTITUENT.LOOKUPID CONSTITUENTLOOKUPID,                          
               ''http://www.blackbaud.com/FROMSPONSORSHIPID?FROMSPONSORSHIPID='' + CONVERT(nvarchar(36),FROMSPONSORSHIP.ID) FROMSPONSORSHIPID,
               dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) FROMOPPORTUNITY,         
                  ''http://www.blackbaud.com/FROMOPPORTUNITYID?FROMOPPORTUNITYID='' + CONVERT(nvarchar(36),FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) FROMOPPORTUNITYID,
               FROMOPPORTUNITY.LOOKUPID FROMOPPORTUNITYLOOKUPID,                     
               ''http://www.blackbaud.com/TOSPONSORSHIPID?TOSPONSORSHIPID='' + CONVERT(nvarchar(36),TOSPONSORSHIP.ID) TOSPONSORSHIPID,
               dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(TOSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) TOOPPORTUNITY,         
                 ''http://www.blackbaud.com/TOOPPORTUNITYID?TOOPPORTUNITYID='' + CONVERT(nvarchar(36),TOSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) TOOPPORTUNITYID,
               TOOPPORTUNITY.LOOKUPID TOOPPORTUNITYLOOKUPID
        from dbo.' + @TABLENAME + ' SUCCESSTAB 
        inner join dbo.SPONSORSHIP FROMSPONSORSHIP on FROMSPONSORSHIP.ID = SUCCESSTAB.FROMSPONSORSHIPID
        inner join dbo.SPONSORSHIPOPPORTUNITY FROMOPPORTUNITY on FROMOPPORTUNITY.ID = FROMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        inner join dbo.SPONSORSHIP TOSPONSORSHIP on TOSPONSORSHIP.ID = SUCCESSTAB.TOSPONSORSHIPID
        inner join dbo.SPONSORSHIPOPPORTUNITY TOOPPORTUNITY on TOOPPORTUNITY.ID = TOSPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = FROMSPONSORSHIP.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;