Best way to do serializable updates to a table

Question

I'm a long time Firebird user and it has a feature called Generators (I think Oracle also has it and it's called Sequences). I'm new to SQL Server and I need to simulate the same feature. I can't use an identity field to solve my problem. I need a named series of values, not a unique number for each row.

My biggest concern was about more than one user calling the procedure at the same time and getting duplicated values, so I decided to lock using the SERIALIZABLE isolation level.

So I came up with this code (I'm using SQL Server 2005):

CREATE TABLE dbo.GENERATORS
    (
      [NAME] VARCHAR(30) NOT NULL,
      [VALUE] INT,
      CONSTRAINT UNQ_GENERATORS_NAME UNIQUE NONCLUSTERED ( [NAME] )
    )
GO

CREATE PROCEDURE GEN_ID
    (
      @GENERATOR_NAME VARCHAR(30)
    )
AS 
    DECLARE @RETURN_VALUE INT ;
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
    UPDATE  GENERATORS
    SET     [VALUE] = [VALUE] + 1
    WHERE   [NAME] = @GENERATOR_NAME
    IF @@ROWCOUNT = 0 
        BEGIN
            INSERT  INTO dbo.GENERATORS ( [NAME], [VALUE] )
            VALUES  ( @GENERATOR_NAME, 1 )
            SET @RETURN_VALUE = 1
        END
    ELSE 
        BEGIN
            SELECT  @RETURN_VALUE = [VALUE]
            FROM    GENERATORS
            WHERE   [NAME] = @GENERATOR_NAME
        END
    COMMIT TRANSACTION
    RETURN @RETURN_VALUE
GO

So my questions are:

• Is this a good solution?
• Is there any better way?

Thanks.

Answer 1

Instead of UPDATE and SELECT, to get the value back that you just added, you can do

UPDATE  GENERATORS
SET     @RETURN_VALUE = [VALUE] = [VALUE] + 1
WHERE   [NAME] = @GENERATOR_NAME

which may remove the need for isolation level

I would prefer to return @RETURN_VALUE as an OUTPUT parameter, rather than as a Return value - leaving the Return Value free for any error code during execution.