>> What I don't know is how SQL handles this behind the scenes. Will SQL execute and complete the entire SP, before any further inserts are allowed?

You could still see inconsistent results. When SQL Server is reading data, it puts a SHARED lock on the resource (row, page, or table) until the resource has been read. The SHARED lock prevents another thread from updating the resource while it's being read. The issue is that the the lock is removed as soon as the resource is done being read. So the lock on the row will be removed once the data on the row is removed. This behavior is the same whether the query is executed from within an ad-hoc query or stored procedure.

This behavior is goverened by the TRANSACTION ISOLATION level. The way only way to get the behavior that you're looking for is to change the TRANSACTION ISOLATION to REPEATABLE READ or SERIALIZABLE (this will cause SQL Server to maintain locks until the end of the transaction) and enclose all the read queries (SELECT) and modification queries (INSERT, UPDATE, and DELETE) within a user transaction.

-Mike