Trigger is probably better, or put into a temp table and roll it all into a single transaction in case of failure.

BEGIN TRAN

--Create new locations and capture Location Name and new LocationIDs, AreaIDs in temp table _NewLocationis

SELECT LocationName, LocationOrder, LocationNotes, @NewAreaID, NumberInArea

INTO #LOCATIONS

FROM Locations

WHERE AreaID=@AreaID

--ORDER BY LocationID

INSERT INTO Locations ( [LocationName], [LocationOrder], [LocationNotes], [AreaID], [NumberInArea] )

--Add new inserted Locations into temp table _NewLocations

--OUTPUT INSERTED.LocationID, INSERTED.LocationName, INSERTED.AreaID INTO _NewLocations (LocationID, LocationName,AreaID)

SELECT LocationName, LocationOrder, LocationNotes, @NewAreaID, NumberInArea

FROM #LOCATIONS

IF @@ERROR<> 0

ROLLBACK

INSERT INTO _NewLocations (LocationID, LocationName,AreaID)

SELECT LocationID, LocationName, AreaID

FROM #LOCATIONS

IF @@ERROR<> 0

ROLLBACK

ELSE

COMMIT