i have a counter in my table that contains the next sequence number for
line items in my application (i know about identity columns.... i can't
use it in this case).

anyway,  i want to write three stored procedures.  one (a select
procedure) allows a user to select the next line item counter to use a
2nd stored procedure (update procedure) updates the counter first by
selecting and then updating the table counter.   The 3rd would delete
the counter row.

how can i prevent a case where the user requests the line item counter
before the update/delete of the counter finishes so they don't get a
counter that is no longer valid?   do i use updlock or tablockx on the
counter table?

Top 100 ... Order by in views SQL 2005 problem
Assistance with SQL Script
SQL 2005: Why does DECLARE varchar(MAX) in Stored Procedure not work?
HOW TO: PIVOT for unknown number of column headings?
cumulative problem