USP_RESERVATION_REMOVEFACILITIES

        create procedure dbo.USP_RESERVATION_REMOVEFACILITIES
        (
            @ID uniqueidentifier
        )
        as
        begin
            declare @SALESORDERITEMS table
            (
                ID uniqueidentifier
            )

            insert into @SALESORDERITEMS
            select
                ITINERARYITEMLOCATION.SALESORDERITEMID
            from dbo.ITINERARYITEMLOCATION
            inner join dbo.ITINERARYITEM on
                ITINERARYITEM.ID = ITINERARYITEMLOCATION.ID
            inner join dbo.ITINERARY on    
                ITINERARYITEM.ITINERARYID = ITINERARY.ID
            where
                ITINERARY.RESERVATIONID = @ID

            declare @e int;
            declare @contextCache varbinary(128);

            set @contextCache = CONTEXT_INFO();

            with LOCATIONDEL_CTE as
            (
                select
                    ITINERARYITEMLOCATION.ID
                from dbo.ITINERARYITEM
                inner join dbo.ITINERARYITEMLOCATION on
                    ITINERARYITEM.ID = ITINERARYITEMLOCATION.ID
                inner join dbo.ITINERARY on    
                    ITINERARYITEM.ITINERARYID = ITINERARY.ID
                where
                    ITINERARY.RESERVATIONID = @ID
            )
            delete from dbo.ITINERARYITEMLOCATION
            from LOCATIONDEL_CTE
            where
                LOCATIONDEL_CTE.ID = ITINERARYITEMLOCATION.ID

            delete from dbo.SALESORDERITEM
            where
                ID in (select ID from @SALESORDERITEMS)

            if not @contextCache is null
                set CONTEXT_INFO @contextCache

            select @e=@@error;

            if @e<>0 return -456; --always return non-zero sp result if an error occurs    


        end