T-SQL stored procedure gets the same value multiple times from a counter

Question

I created a stored procedure that should return a unique number, increasing the value in the table each time it is called.

ALTER PROCEDURE [dbo].[GetNextNumber] @Next_Number char(9) output AS BEGIN BEGIN TRAN DECLARE @Current_Number int SET @Current_Number = (SELECT CONVERT(int, Counter) FROM CounterTable) IF(@Current_Number IS NULL) BEGIN SET @Next_Number = '000000000' INSERT INTO CounterTable (Counter) VALUES (@Next_Number) END ELSE IF(@Current_Number >= 999999999) BEGIN SET @Next_Number = '000000000' UPDATE dbo.CounterTable SET Counter = @Next_Number END ELSE BEGIN SET @Next_Number = REPLACE(STR(@Current_Number + 1, 9),' ', '0') UPDATE dbo.CounterTable SET Counter = @Next_Number END COMMIT TRAN END 

Sometimes a stored procedure returns the same number twice. What I did wrong?

Answer 1

I have done this before:

 update CounterTable set @Next_Number = Counter = Counter + 1 

which stores it in a single expression, and therefore (if you don't switch to Read Uncommitted, maybe) there should be no problem with multiple callers.

Answer 2

You can explicitly lock Countertable using the help table

 ... SET @Current_Number = ( SELECT CONVERT(int, Counter) FROM CounterTable WITH(Holdlock, Updlock) ) ...