Running the same stored procedure sequentially

Use the following system stored procedure to block the same stored procedure from running concurrently.

   sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

   sp_releaseapplock

Note:

• The above system stored procedures must be running within a database transaction.
• If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called. 
• If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.