UFN_SPONSORSHIPOPPORTUNITIES_FOR_PROGRAM

            CREATE function dbo.UFN_SPONSORSHIPOPPORTUNITIES_FOR_PROGRAM(@ID uniqueidentifier)
            returns int
            with execute as caller
            as begin
                declare @COUNT int = 0;      

                declare @PROGRAMFILTEREDLOCATION table    
                (    
                    HIERARCHYPATH hierarchyid    
                )    

                insert into @PROGRAMFILTEREDLOCATION (HIERARCHYPATH)    
                select 
                    FILTERLOCATION.HIERARCHYPATH    
                from dbo.SPONSORSHIPPROGRAM    
                cross apply dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(SPONSORSHIPPROGRAM.FILTERLOCATIONS) X      
                inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID    
                where    
                    SPONSORSHIPPROGRAM.ID = @ID    

                select @COUNT = count(*)    
                from dbo.SPONSORSHIPOPPORTUNITY      
                inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID      
                left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID      
                where     
                    SPONSORSHIPPROGRAM.ID = @ID and     
                    (    
                        SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 0 or      
                        (    
                            SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 1 and      
                            exists    
                            (    
                                select 'x'      
                                from @PROGRAMFILTEREDLOCATION PROGRAMFILTEREDLOCATION    
                                where     
                                OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PROGRAMFILTEREDLOCATION.HIERARCHYPATH) = 1    
                            )
                        ) or      
                        (    
                            SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 2 and      
                            not exists    
                            (    
                                select 'x'      
                                from @PROGRAMFILTEREDLOCATION PROGRAMFILTEREDLOCATION    
                                where     
                                OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PROGRAMFILTEREDLOCATION.HIERARCHYPATH) = 1    
                            )    
                        )    
                    )

                return @COUNT      
            end