USP_REGISTRANT_DELETE

                    CREATE procedure dbo.USP_REGISTRANT_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as
                        set nocount on;

                        --check deletion rules, if any


                        declare @contextCache varbinary(128);
                        declare @registrantCount int;
                        declare @eventID uniqueidentifier;
                        declare @constituentID uniqueidentifier;

                        if @CHANGEAGENTID is null
                                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

                        begin try
                            /* cache current context information */
                            set @contextCache = CONTEXT_INFO();

                            /* set CONTEXT_INFO to @CHANGEAGENTID */
                            set CONTEXT_INFO @CHANGEAGENTID;

                            select
                                @eventID = EVENTID,
                                @constituentID = CONSTITUENTID
                            from dbo.REGISTRANT
                            where ID = @ID;

                            select
                                @registrantCount = count(id)
                            from dbo.REGISTRANT
                            where
                                EVENTID = @eventID
                                and CONSTITUENTID = @constituentID;

                            --There is only one REGISTRANTTRAVEL and/or REGISTRANTLODGING record for each registrant.

                            --There maybe more than one REGISTRANT record for an EVENTID/CONSTITUENTID combo.

                            --Don't delete the REGISTRANTTRAVEL or REGISTRANTLODGING records if another REGISTRANT record is related to them.

                            if @registrantCount < 2
                            begin
                                -- Delete registrant travel records

                                delete from 
                                    dbo.REGISTRANTTRAVEL
                                from 
                                    dbo.REGISTRANT
                                    left join REGISTRANTTRAVEL 
                                        on REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
                                where REGISTRANT.ID = @ID
                                    and REGISTRANTTRAVEL.MAINEVENTID = REGISTRANT.EVENTID;

                                -- Delete registrant lodging records

                                delete from 
                                    dbo.REGISTRANTLODGING
                                from 
                                    dbo.REGISTRANT
                                    left join REGISTRANTLODGING 
                                        on REGISTRANTLODGING.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
                                where REGISTRANT.ID = @ID
                                    and 
                                    (
                                        REGISTRANTLODGING.MAINEVENTID = REGISTRANT.EVENTID --MAINEVENTID is no longer used, keep it here just in case

                                        or REGISTRANTLODGING.EVENTID = REGISTRANT.EVENTID
                                    );
                            end

                            -- Delete group member record

                            delete from dbo.EVENTGROUPMEMBER where REGISTRANTID = @ID;

                            -- Delete guest's member record

                            delete from dbo.EVENTGROUPMEMBER where EVENTGROUPMEMBER.REGISTRANTID in(select ID from dbo.REGISTRANT where REGISTRANT.GUESTOFREGISTRANTID = @ID);

                            -- Delete any seats for the registrant and guests

                            update dbo.EVENTSEATINGSEAT set 
                                EVENTSEATINGSEAT.REGISTRANTID = null,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where 
                                EVENTSEATINGSEAT.REGISTRANTID = @ID or 
                                exists(select GUEST.ID from dbo.REGISTRANT as GUEST where GUEST.GUESTOFREGISTRANTID = @ID and GUEST.ID = EVENTSEATINGSEAT.REGISTRANTID);

                            -- Delete any team fundraising information for this registrant if this is a team fundraising event

                            -- Deleting from TEAMFUNDRAISER cascade deletes from TEAMFUNDRAISINGTEAMMEMBER

                            delete from
                                dbo.TEAMFUNDRAISER
                            from
                                dbo.REGISTRANT
                                left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
                                left join dbo.TEAMFUNDRAISER on
                                    REGISTRANT.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
                                    and EVENT.APPEALID = TEAMFUNDRAISER.APPEALID
                            where
                                REGISTRANT.ID = @ID
                                or REGISTRANT.GUESTOFREGISTRANTID = @ID;

                            delete from
                                dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                            from
                                dbo.REGISTRANT
                                left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
                                left join dbo.TEAMFUNDRAISINGTEAM on EVENT.APPEALID = TEAMFUNDRAISINGTEAM.APPEALID
                                left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
                                    REGISTRANT.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
                                    and TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
                            where
                                REGISTRANT.ID = @ID
                                or REGISTRANT.GUESTOFREGISTRANTID = @ID;

                            -- Delete any registrant registration maps for this registrant and their guests

                            -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

                            delete from
                                dbo.REGISTRANTREGISTRATIONMAP
                            from
                                dbo.REGISTRANT
                                left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
                            where
                                REGISTRANT.ID = @ID
                                or REGISTRANT.GUESTOFREGISTRANTID = @ID;

                            -- Delete any guests of this registrant

                            delete from dbo.REGISTRANT where REGISTRANT.GUESTOFREGISTRANTID = @ID;

                            /* reset CONTEXT_INFO to previous value */
                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            --Delete pending sales order items that used registrantid

                            declare @ORDERITEM uniqueidentifier = null
                            declare deletesalesitems_cursor cursor local fast_forward for 
                                select [ID] 
                                from dbo.[SALESORDERITEM] 
                                where exists (
                                    select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
                                    from dbo.[SALESORDERITEMEVENTREGISTRATION]
                                    inner join dbo.[SALESORDERITEM] as [SOI]
                                        on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SOI].[ID]
                                    inner join dbo.[SALESORDER]
                                        on [SOI].[SALESORDERID] = [SALESORDER].[ID]
                                    where 
                                        [SALESORDER].[STATUSCODE] = 0 and
                                        [SOI].[ID] = [SALESORDERITEM].[ID] and
                                        [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = @ID
                                )

                            open deletesalesitems_cursor

                            fetch next from deletesalesitems_cursor
                            into @ORDERITEM

                            while @@FETCH_STATUS = 0
                            begin
                                exec dbo.USP_SALESORDERITEM_DELETE @ORDERITEM, @CHANGEAGENTID

                                fetch next from deletesalesitems_cursor
                                into @ORDERITEM
                            end

                            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                            close deletesalesitems_cursor;
                            deallocate deletesalesitems_cursor;

                            exec USP_REGISTRANT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                            return 0;
                        end try

                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch