USP_REPORT_ADDREQUESTSFORMULTIPLESTUDENTS_PROCESSINGREPORT

create procedure dbo.USP_REPORT_ADDREQUESTSFORMULTIPLESTUDENTS_PROCESSINGREPORT
(
    @ID nvarchar(36) 
)
with execute as owner
as
    set nocount on
    declare @TABLENAME nvarchar(255)
    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?STUDENTID='' + CONVERT(nvarchar(36), EX.STUDENTID) as STUDENTID,
                        STUDENT_NAME, 
                        ''http://www.blackbaud.com?COURSEID='' + CONVERT(nvarchar(36), EX.COURSEID) as COURSEID,
                        COURSE_NAME,
                        SCHOOL_NAME,
                        SCHOOLGRADELEVEL,
                        COURSEINFO,
                        ERRORMESSAGE                                
                    from dbo.' + 
                        @TABLENAME + ' as EX
                    left join dbo.CONSTITUENT on EX.STUDENTID = CONSTITUENT.ID
                    left join dbo.COURSE on EX.COURSEID = COURSE.ID

                order by
                    CONSTITUENT.KEYNAME, ERRORMESSAGE, COURSE_NAME'                        

        exec sp_executesql @SQL
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch