I need to read and increment a value atomically in SQL Server 2008, using c#.
For example, I have to 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);
@variable = column = expression is specifically allowed in the UPDATE Grammar and in the comments it says SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.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.
identity?