Prevent a Stored Procedure from being executed twice at the same time

yes there is a way. use what is known as SQL Server Application locks.

EDIT: yes this also works in SQL Server 2000.

You can use sp_getapplock sp_releaseapplock as in the example found at Lock a Stored Procedure for Single Use Only.

But, is that what you are really trying to do? Are you trying to get a transaction with a high isolation level? You would also likely be much better off handling that type of concurrency at the application level as in general higher level languages have much better primitives for that sort of thing.