UFN_EVENT_GETCAPACITY

CREATE function dbo.UFN_EVENT_GETCAPACITY(@ID uniqueidentifier)
returns integer
with execute as caller
as begin

    -- program event

    if (select PROGRAMID from dbo.EVENT where EVENT.ID = @ID) is not null
        return (select AVAILABILITY
            from dbo.V_PROGRAMEVENT_TICKETCOUNTS
            where ID = @ID);

    -- fundraising event

    declare @CAPACITY integer
    declare @TOTALREGISTRANT integer

    select @CAPACITY = CAPACITY from dbo.EVENT where EVENT.ID = @ID;

    if @CAPACITY = 0
        return 0;

    select @TOTALREGISTRANT = COUNT(*)
        from dbo.REGISTRANT 
        where REGISTRANT.EVENTID = @ID and REGISTRANT.ISCANCELLED = 0;

    if @CAPACITY > @TOTALREGISTRANT     
        return @CAPACITY - @TOTALREGISTRANT;

    return 0;
end