Stored procedure help

[TPetersonFlorida]

I have a database that is used by an application where multiple people are writing to the database. I have one table that stores the next available part number, a sequential number. I need to write a stored procedure that gets that number, increments by one, then writes that new number back to the table for the next person. How do i lock this process so that if there are multiple calls to this stored procedure, only one executes at a time??

Here is my TSQL as of now:

Select @varNum = <PartNumber> From <MyTable>
Set @varNum = @varNum + 1
Update <MyTable> Set <PartNumber> = @varNum

The stored procedure will return @varNum.
Obviously the field name and table name are psuedo names.

Thanks in advance!

 

[NoCoolHandle]

This is exactly what the identity property was designed to do. Why would you try to do this when all your concurrency issues can be fixed using the identity.

If you need to know what number you just added when you added a row use the @@identity function or @@identity_scope to find out what the last added identity number was and then you can use that in a child table.

If you decise you really need to go ahead...


Begin Tran -- All Update and Exclusive locks will remain
-- till the transaction completes
Declare @id int
select @id = lastid + 1 from IDTABLE (XLOCK)
-- the XLOCK will place an exclusive lock on the table
-- till released

stuff you do



--finish transaction (after all you updates etc..)
Commit Tran