USP_REPORT_BILLINGGENERATETRANSACTIONSCHARGEPROCCESSREPORT_EXCEPTIONDATA

                create procedure dbo.USP_REPORT_BILLINGGENERATETRANSACTIONSCHARGEPROCCESSREPORT_EXCEPTIONDATA
                (
                    @ID nvarchar(36) -- Process ID

                )
                as
                begin
                    -- Get the exception table name

                    declare @DATA_TABLENAME nvarchar(255);
                    select @DATA_TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
                            from dbo.BUSINESSPROCESSSTATUS 
                            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
                            where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION';

                    if ((@DATA_TABLENAME is null) or (OBJECT_ID(@DATA_TABLENAME) is null))
                    begin
                        raiserror('Business process results table could not be found.  The process might not have completed successfully. ',13,1);
                    end

                    -- Retrieve the exception data

                    begin try
                        declare @SQL nvarchar(4000);
                        set @SQL = 'select ''http://www.blackbaud.com?STUDENTID='' + CONVERT(nvarchar(36), DATA.STUDENTID) as STUDENTID,
                                           DATA.STUDENTNAME,
                                           DATA.GRADELEVEL,
                                           DATA.SCHOOL,
                                           DATA.BILLINGITEMNAME,
                                           DATA.AMOUNT,
                                           DATA.DUPLICATECHARGEID,
                                           ''http://www.blackbaud.com?DUPLICATETRANSACTIONID='' + CONVERT(nvarchar(36), DATA.DUPLICATETRANSACTIONID) as DUPLICATETRANSACTIONID,
                                           case DATA.ERRORMESSAGE when ''DUPLICATE'' then ''Charge already exists'' else '''' end as EXCEPTION
                                    from ' + @DATA_TABLENAME + ' as DATA';

                        exec sp_executesql @SQL;
                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch    
                end