CREATE PROCEDURE sp_sel_nextNumber @nvcObjectName nvarchar(80), @intNextNumber int OUTPUT AS -- Force the Object Name string to lower case. SET @nvcObjectName = LOWER( @nvcObjectName ) BEGIN TRANSACTION -- Query the nextNumber table for the next available number. -- Force and Exclusive lock on the row if it's found. SELECT @intNextNumber = intNextNumber FROM tblNextNumber WITH (XLOCK, ROWLOCK) WHERE nvcObjectName = @nvcObjectName IF @@ROWCOUNT = 0 BEGIN SET @intNextNumber = 1 INSERT INTO tblNextNumber ( nvcObjectName, intNextNumber ) VALUES ( @nvcObjectName, @intNextNumber ) END ELSE BEGIN SET @intNextNumber = @intNextNumber + 1 -- Increment the next id counter UPDATE tblNextNumber SET intNextNumber = @intNextNumber WHERE nvcObjectName = @nvcObjectName END COMMIT TRANSACTION GOI am using the locking hints "WITH (XLOCK, ROWLOCK)" in the select statement to prevent other users from reading the same row before my stored procedure can increment the value. However, this technique is creating an excessive amount of deadlocks.