USP_KPI_EVENTS_REGISTRANT_CAPACITY_GOAL

CREATE procedure dbo.USP_KPI_EVENTS_REGISTRANT_CAPACITY_GOAL
    @VALUE int output, 
    @EVENTID uniqueidentifier,
    @INCLUDESUBEVENTS bit = 0

as

    set nocount on;

    SET @value=0;

    declare @EVENTS table
    (
        ID uniqueidentifier
    )

    if @INCLUDESUBEVENTS = 0
    begin
        insert into @EVENTS
        select @EVENTID
    end
    else begin
        insert into @EVENTS
            select RELATEDEVENT.ID
            from dbo.EVENTHIERARCHY as RELATEDEVENT
                inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
            where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1
    end

    select @VALUE=sum(CAPACITY) from dbo.EVENT                        
    where  ID in (select ID from @EVENTS);