create procedure [dbo].[MssWebInsertOAndILocalServiceLocations]
    @inOrderId int,
    @inLocalServiceIds intList readonly,
    @inLoctionIds intList readonly
as
begin
    set nocount on
    ;with LocalServiceAndLocationIds as(
        select
            localServiceId = localServices.Item ,
            locationId =locations.Item
        from @inLocalServiceIds localServices
        cross join @inLoctionIds locations
    )
    merge LocServOrderLocationMap with (tablock) using LocalServiceAndLocationIds on
    LocServOrderLocationMap.LocServFId = LocalServiceAndLocationIds.localServiceId and
    LocServOrderLocationMap.OrderLocationFId = LocalServiceAndLocationIds.locationId
    when not matched then
        insert ( LocServFId, OrderLocationFId)
        values (LocalServiceAndLocationIds.localServiceId,LocalServiceAndLocationIds.locationId);
    
    --if location associated to local service as origin then remove the association
    ;with LocalServiceAndLocationIds as(
        select
            localServiceId = localServices.Item ,
            locationId =locations.Item
        from @inLocalServiceIds localServices
        cross join @inLoctionIds locations
    )
    update LocServ set OriginOrderLocationFID = null
    from LocServ
    inner join LocalServiceAndLocationIds on LocalServiceAndLocationIds.localServiceId = LocServ.prikey and
    LocServ.OriginOrderLocationFID = LocalServiceAndLocationIds.locationId;

    --if location associated to local service as destination then remove the association
    ;with LocalServiceAndLocationIds as(
        select
            localServiceId = localServices.Item ,
            locationId =locations.Item
        from @inLocalServiceIds localServices
        cross join @inLoctionIds locations
    )
    update LocServ set DestinationOrderLocationFID = null
    from LocServ
    inner join LocalServiceAndLocationIds on LocalServiceAndLocationIds.localServiceId = LocServ.prikey and
    LocServ.DestinationOrderLocationFID = LocalServiceAndLocationIds.locationId;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertOAndILocalServiceLocations] TO [MssExec]
GO