I have a very large stored procedure (called pr_insert_order) that saves an order into our database. Since it has a lot of moving parts, it is wrapped in a BEGIN TRANSACTION and COMMIT TRANSACTION. That way if anything blows up in the middle of the procedure, the whole transaction is automatically rolled back.

My problem is that we use counters in our system for things like "Next sequence number", "Next order number", "Next line item number", etc. So we have a stored procedure (called NextVal) that we call and it passes us the next available number in the sequence. In order to accomplish this, it throws a record lock on the row in a tiny utility table (SysData) that holds the next available sequence number. It grabs that number to return to the calling proc, then updates the row to the next number so the next time it is called it will return the next higher number.

Since the NextVal proc is called from within pr_insert_order, and pr_insert_order starts with BEGIN TRANSACTION and ends with COMMIT TRANSACTION, the update that takes place within NextVal gets tied into the overall transaction of pr_insert_order. As a result, two people running pr_insert_order at the same time can get stacked behind one another, due to the open lock on SysData created by the first call to NextVal by the first user in the mix.

I need a way to grab a next value without throwing locks on a table that will cause successive runs of a proc to get stalled behind one another. I considered doing an external procedure call to another instance of SQL server running on the same physical server, but that is way too much overhead for just one litttle table - i would have to have every customer re-install SQL to get the second instance - a nightmare.

I do not want to use an identity field in the tables to get a sequence number. This would present many additional challenges that are worse than my current problem.

I appreciate any help anyone can provide me. Below is a simplified code snippet from NextVal:

CREATE PROCEDURE NextVal
AS
DECLARE @x INT
UPDATE SysData set @x = val + 1, val = val + 1
RETURN @x

Thank You,

John