USP_REPORT_ADDREQUESTSFORMULTIPLESTUDENTS_PROCESSINGREPORT_SUMMARY

create procedure dbo.USP_REPORT_ADDREQUESTSFORMULTIPLESTUDENTS_PROCESSINGREPORT_SUMMARY
(
    @ID nvarchar(36) 
)
with execute as owner
as
    set nocount on
    declare @SUCCESS_TABLENAME nvarchar(255)
    declare @EXCEPTION_TABLENAME nvarchar(255)
    declare @SQL nvarchar(4000)

    select @SUCCESS_TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
            from dbo.BUSINESSPROCESSSTATUS 
            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
            where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'SUCCESS'

    select @EXCEPTION_TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
            from dbo.BUSINESSPROCESSSTATUS 
            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
            where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION'

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

    begin try
        --we want grade level and success num of requests

        set @SQL = 'select 
                        distinct SCHOOLGRADELEVEL, 
                        SCHOOLGRADELEVELSEQUENCE,
                        (select COUNT(1) from dbo.' + @SUCCESS_TABLENAME + ' where SCHOOLGRADELEVEL = SUMMARY.SCHOOLGRADELEVEL) as SUCCESS_COUNT,
                        (select COUNT(1) from 
                            (select STUDENTID, COURSEID 
                             from dbo.' + @EXCEPTION_TABLENAME + ' 
                             where SCHOOLGRADELEVEL = SUMMARY.SCHOOLGRADELEVEL 
                             group by STUDENTID, COURSEID) as E) as EXCEPTION_COUNT 
                    from (                    
                        select SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE from dbo.' + @SUCCESS_TABLENAME + ' as SUCCESS 
                    union 
                        select SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE from dbo.' + @EXCEPTION_TABLENAME + ' as EXCEPTION) as SUMMARY
                    order by SCHOOLGRADELEVELSEQUENCE'                    

        exec sp_executesql @SQL
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch