This post has already been read 847 times!
LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting
LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run within this particular setting. It has to be a single execution of Dynamic SQL since the setting will revert back to the value of the top-most / outer-most process. For example:
Run this in one query tab in SSMS:
Run the following in another query tab in SSMS:
Now go back to the first query tab (where you created the two temporary Stored Procedures) and run the following two tests:
Now go back to the second query tab and execute the