USP_EVENTSEATING_GUESTSNOTSEATEDWITHHOST

            CREATE procedure dbo.USP_EVENTSEATING_GUESTSNOTSEATEDWITHHOST
            (
                @EVENTID uniqueidentifier,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as caller
            as

            declare @CURRENTAPPUSERID uniqueidentifier;
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE bit;

            set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
            set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
            set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                select 
                    dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) as REGISTRANTNAME,
                    dbo.UFN_CONSTITUENT_BUILDNAME(GUESTOF.CONSTITUENTID) as HOSTNAME,
                    dbo.UFN_EVENTSEATING_GETREGISTRANTSEATPATH(REGISTRANT.ID) as GUESTSEAT, 
                    dbo.UFN_EVENTSEATING_GETREGISTRANTSEATPATH(GUESTOF.ID) as HOSTSEAT
                from 
                    dbo.REGISTRANT
                    inner join dbo.EVENTSEATINGSEAT on EVENTSEATINGSEAT.REGISTRANTID = REGISTRANT.ID
                    inner join dbo.REGISTRANT as GUESTOF on GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID
                where
                    REGISTRANT.GUESTOFREGISTRANTID IS NOT NULL and 
                    GUESTOF.WILLNOTATTEND = 0 and
                    exists(select 
                                GUESTOFSEAT.ID 
                            from 
                                dbo.EVENTSEATINGSEAT as GUESTOFSEAT 
                            where 
                                GUESTOFSEAT.REGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID) and
                    EVENTSEATINGSEAT.EVENTSEATINGSUBSECTIONID <> (select 
                                                                        GUESTOFSEAT.EVENTSEATINGSUBSECTIONID 
                                                                    from 
                                                                        dbo.EVENTSEATINGSEAT as GUESTOFSEAT 
                                                                    where 
                                                                        GUESTOFSEAT.REGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID) and
                    REGISTRANT.EVENTID = @EVENTID
                    and (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REGISTRANT.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                            and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, GUESTOF.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1));