SQL Server concurrency and generated sequence

I need a sequence of numbers for an application, and I am hoping to leverage the abilities of SQL Server to do it. I have created the following table and procedure (in SQL Server 2005):

CREATE TABLE sequences (
  seq_name varchar(50) NOT NULL,
  seq_value int NOT NULL
)

CREATE PROCEDURE nextval
  @seq_name varchar(50)
AS
BEGIN
  DECLARE @seq_value INT

  SET @seq_value = -1

  UPDATE sequences
  SET @seq_value = seq_value = seq_value + 1
  WHERE seq_name = @seq_name

  RETURN @seq_value
END

I am a little concerned that without locking the table/row another request could happen conc开发者_如何学Gourrently and end up returning the same number to another thread or client. This would be very bad obviously. Is this design safe in this regard? Is there something I can add that would add the necessary locking to make it safe?

Note: I am aware of IDENTITY inserts in SQL Server - and that is not what I am looking for this in particular case. Specifically, I don't want to be inserting/deleting rows. This is basically to have a central table that manages the sequential number generator for a bunch of sequences.

The UPDATE will lock the row exclusively so your concurrency concerns are not founded. But use of @variable assignment in UPDATE statements is relying on undefined behavior. It's true, it will work, but rather rely on defined behavior: use the OUTPUT clause.

CREATE PROCEDURE nextval
  @seq_name varchar(50)
 , @seq_value INT output
AS
BEGIN
  DECLARE @ot TABLE (seq_value INT)

  UPDATE sequences
  SET seq_value = seq_value + 1
  OUTPUT INSERTED.seq_value INTO @ot
  WHERE seq_name = @seq_name

  SELECT @seq_value = seq_value FROM @ot;
END

Try this code:

ALTER PROCEDURE Item_Category_Details


@Item_Category varchar(20)
,@Active VARCHAR(10)

AS 

DECLARE @Item_Category_Code varchar(20)
DECLARE @seqNo AS INTEGER
SELECT @seqNo=ISNULL(Item_Code,0) FROM Seq_Master
SET @seqNo=@seqNo+1
SET @Item_Category_Code=@seqNo

PRINT @Item_Category_Code

INSERT Item_Category_Master
(Item_Category_Code
,Item_Category
,Active
)
VALUES
(
@Item_Category_Code
,@Item_Category
,@Active
)
UPDATE Seq_Master SET Item_Code=@seqNo