It seems like perfect use for Sequence

Another option would be to use OUTPUT clause of UPDATE statement

The third otion would be to use UPDATE wit a local variable. The code has been posted a few tomes in thee past

>
>I am trying to lock a table row in a table that manages field no sequences
>
>Here's my code:
>
>

BEGIN TRAN
>
>SELECT * FROM NEXTID WITH (HOLDLOCK, ROWLOCK) WHERE Cdbfname = 'JOBNO'
>
>mjobno=sqlResults.iNextNo
>
>UPDATE NEXTID SET iNextNo=InextNo+1 FROM NEXTID WITH (HOLDLOCK, ROWLOCK) WHERE Cdbfname = 'JOBNO'
>
>/* Do all your stuff here while the record is locked */
>
>COMMIT TRAN
>

>
>Basically I want one user to go through this code at a time, I thought the begin tran would
>do what I want?
>
>EDIT: I have selected Internet application in the desc, but it is should say desktop application :)
>
>Any help extremely appreciated