USP_DATALIST_EVENTTOTALBYEVENTID

CREATE procedure dbo.USP_DATALIST_EVENTTOTALBYEVENTID(@EVENTID uniqueidentifier)
as
begin
    set nocount on;

    declare @result table
    (
        GOAL money,
        TOTALAMOUNTRAISED money
    );

    -- try retrieving data from cache first

    set transaction isolation level read uncommitted;
    begin transaction;

    declare @IDOC int;
    declare @CACHEKEY as nvarchar(512);
    declare @CACHEDATA as xml;
    declare @LIFESPANSECONDS int;

    set @LIFESPANSECONDS = 60;
    set @CACHEDATA = null;
    set @CACHEKEY = 'EVENTTOTALBYEVENTID';

    exec dbo.USP_FAFEVENTDATACACHE_GET @DATA = @CACHEDATA output, @EVENTID = @EVENTID, @CACHEKEY = @CACHEKEY;

    if @CACHEDATA is null    -- start calculations

        begin
            -- update cache ISCALCULATING flag

            exec dbo.USP_FAFEVENTDATACACHE_CALCULATIONSTARTS @EVENTID = @EVENTID, @CACHEKEY = @CACHEKEY;

            insert into @result(GOAL, TOTALAMOUNTRAISED)
            select REVENUETOTAL AS GOAL, dbo.UFN_REVENUE_GETEVENTTOTAL(@EVENTID) TOTALAMOUNTRAISED
            from dbo.EVENTGOAL
            where EVENTID = @EVENTID

            -- convert the result to xml

            set @CACHEDATA = (select * from @result for xml raw);
            if @CACHEDATA is null
                set @CACHEDATA = '<rows />';
            else
                set @CACHEDATA= '<rows>' + convert(nvarchar(max), @CACHEDATA) + '</rows>';

            select @LIFESPANSECONDS = CONVERT(int, ISNULL(Value, '60')) from dbo.SETTING where Name = 'FAFEventTotalCache';

            -- cache the result

            exec dbo.USP_FAFEVENTDATACACHE_ADD
                @EVENTID = @EVENTID,
                @CACHEKEY = @CACHEKEY,
                @DATA = @CACHEDATA,
                @LIFESPANSECONDS = @LIFESPANSECONDS;            
        end
    else
        begin
            begin try
                exec sp_xml_preparedocument @IDOC output, @CACHEDATA;

                insert into @result
                select * from openxml(@IDOC, '/rows/row', 0) with (
                    GOAL money,
                    TOTALAMOUNTRAISED money
                );

                exec sp_xml_removedocument @IDOC;
            end try
            begin catch
                -- do nothing

            end catch        
        end

    commit transaction;

    select GOAL, TOTALAMOUNTRAISED from @result
end