USP_REPORT_BILLINGGENERATETRANSACTIONSCHARGEPROCCESSREPORT_SUCCESSDATA

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

                )
                as
                begin
                    -- Get the success 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 = 'SUCCESS';

                    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 success 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.CHARGEID as CHARGEID,
                                           ''http://www.blackbaud.com?TRANSACTIONID='' + CONVERT(nvarchar(36), DATA.TRANSACTIONID) as TRANSACTIONID,
                                           DATA.AMOUNT
                                    from ' + @DATA_TABLENAME + ' as DATA';

                        exec sp_executesql @SQL;
                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch    
                end