I think you are trying to solve the problem in an incorrect way. What you want is utmost protection of database consistency. If two persons run a stored procedure at the same time, the database consistency may be violated.
To protect against various kinds of database inconsistencies, the SQL standard has four transaction isolation levels:
- READ UNCOMMITTED where basically transactions lose their value, other transactions seeing dirty data. Don't use this!
- READ COMMITTED where transactions see only committed data, but there may be inconsistencies where two transactions can step over each other's toes
- REPEATABLE READ where one kind of inconsistency, non-repeatable read, is solved
- SERIALIZABLE which guarantees that there exists some virtual order in which executing the transactions would lead to the results that their execution resulted in
However, the SQL standard has a locking based approach for these database inconsistencies, and for performance reasons many databases take a snapshot isolation based approach that basically has these levels:
- READ COMMITTED which is the same it's in locking based databases
- SNAPSHOT ISOLATION where database sees a snapshot of all data and if it tries to update a row that has been updated by some other transaction, it is cancelled, yet there are some well-known anomalies that can take place
- SERIALIZABLE which is the same as it's in locking based databases, but this time implemented in a different manner, not by taking locks but by ensuring there are no serialization violations, and if such a violation is detected, cancelling a transaction
The transaction cancellations in these snapshot isolation based databases may sound worrying, but then again every single database will cancel a transaction due to a deadlock, so any reasonable application needs anyway to be able to re-try a transaction.
What you want is the SERIALIZABLE isolation level: it ensures that if transactions executed independently one after another result in a good state, any parallel execution of the transactions also results in a good state. Fortunately, Michael Cahill has in his doctoral dissertation found out how SERIALIZABLE isolation level can be supported by snapshot isolated databases with little effort.
If using a SERIALIZABLE isolation level in a snapshot isolated database, if two people try to run the stored procedure concurrently and they would step on each other's toes, one of the transactions would be cancelled.
Now, does SQL Server genuinely support the SERIALIZABLE isolation level (instead of masquerading snapshot isolation behind the SERIALIZABLE keyword)? Quite frankly, I don't know: the only database I know that supports it is PostgreSQL.
Even though I failed to give SQL Server specific advice, I'm still posting this answer nevertheless, as users of PostgreSQL and users of other databases that can consider switching to PostgreSQL can benefit from my answer. Also, users of non-PostgreSQL databases who can't switch to PostgreSQL can pressure their favorite database vendor to offer genuine SERIALIZABLE isolation level.