Read and Increment int value in SQL Server

I need to read and increment a value atomically in SQL Server 2008, using c#.

For example, I have t开发者_Go百科o insert items of a "lot", for this i need the number of the last lot and be sure, that no one else get this number.

So i have a table only with the last lot number, and find a way to get and update the number in only one step.

How can i do this?

Is it essential that the lot numbers be sequential? Why not just use an identity? This is better in terms of concurrency as otherwise you need to block concurrent insert attempts in case they get rolled back and would leave a gap in the sequence.

If it absolutely is a requirement however you can do

CREATE TABLE dbo.Sequence 
  (
     OneRow CHAR(1) DEFAULT('X') PRIMARY KEY CHECK(OneRow = 'X'),
     val    INT
  )  

Insert a row with an initial seed.

INSERT INTO dbo.Sequence 
            (val)
VALUES     (1)  

Then to allocate a range of sufficient size for your insert (call it in the same transaction obviously)

CREATE PROC dbo.GetSequence
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence 
SET @val = val = (val + @n);

The easiest way would be to just alter the table so that the lot number is an identity field and will auto-increment itself. Then you don't have to worry about incrementing in code.