Question: I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in it should return that value if there has never been a result greater or the next highest integer available. It goes without saying there can be multiple clients calling this SP at the same time.

Given a table MetaInfo with columns MetaKey varchar(max) and MetaValueLong bigInt. It is expected the row with the MetaKey of 'Internal-ID-Last' will contain the last highest value assigned. I created the following stored procedure:

```
CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt 
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    UPDATE MetaInfo WITH (ROWLOCK) 
      SET MetaValueLong = CASE 
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong+1 
                            ELSE @inID+1
                          END 
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong 
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION 

END

```

My question is simply, does this stored procedure work as expected (all callers will be assigned a unique result)?

Answer: 
I've had a look and Microsoft themselves offer a solution without locks:

[SQL Server Sequence Number](https://learn.microsoft.com/en-us/archive/blogs/sqlcat/sql-server-sequence-number)

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add `UPDLOCK` to your `ROWLOCK` (as per ["table as a queue" (SO)](https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001) but without `READPAST`). This will increase isolation in case a second process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. `READPAST` allows safe concurrency but in this case it's useless.

Note: you can use the `OUTPUT` clause instead of a second select then you don't need the transaction.
Following thing are missing 

```
1. SET XACT_ABORT
2. Exception Handling (Try Catch)

```

Yes, It should meet your condition. Once such situations comes in transactions, It creates its multiple instances and subsequently, all callers will be assigned a unique result