Incrementing Values

Occasionally, I run across procedures that mimic the functionality of the IDENTITY property.  There are always various reasons for these procedures to exist, some valid and some not quite so.  Recently while trying to tune one of these procedures that someone had added interesting locking hints to a creative rewrite to the procedure was suggested.

Usually these procedure have a SELECT and then an UPDATE in the procedure to get and set the value.  In the suggested solution the OUTPUT variable would be included in the SET statement.  I’ve attached two sample scripts that includes this type of functionality.  The “IDENTITY_OLD” is the typical way that this is implemented and “IDENTITY_NEW” contains the new method.  The new method is as such:

Update Configuration
Set value = value + 1, @ID = value + 1
Where tablename = @tablename

This code change hasn’t been implemented in the test or production environments yet, because I am still early in reviewing this.  I haven’t been able to find anything wrong with this yet and maybe there isn’t anything to fear.  A few searches on the internet hasn’t turned up anything that resembles the solution, so I ask… does anyone see anything wrong with this?

EDITED: Better scripts make better examples.