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)?