You'll have no problems if you use the following syntax:

GO
CREATE PROCEDURE GetCounter (@TableName varchar(100), @ID int OUT)
AS

UPDATE CounterTable
SET
@ID = LastID + 1,
LastID = LastID + 1
WHERE CounterName = @TableName
GO

Also:

1. RETURN statements in procedures should only return error codes, not values with other meaning (such as the new ID). Use OUTPUT variables for that.

2. UPDLOCK is also needed in the original code for it to work properly with high concurrency.

For reference see http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx#41849

and http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

I am trying to reproduce your "proof" but my result shows @Variable = Column = Expression syntax works perfectly.

My code
--------------------------
BEGIN TRAN

DECLARE @i INT;

SELECT 0 AS One, 0 AS Two
INTO #Temp

UPDATE #Temp
SET @i = One = 1, @i = Two = 2

SELECT *, @i AS Var FROM #Temp

ROLLBACK
--------------------------

The result is One=1, Two=2, @i=2 (on SQL 2005).
Am I missing something?

My apologies. When I first ran that script back in June of 2004 on a SQL 2000 box, it behaved as I described. That Denis didn't check me on it was because he trusted me. (and evidently shouldn't have!)

However, it's still not completely resolved for me. Here is another example of how this syntax may not always work as expected:

CREATE TABLE #Temp (i INT, j INT)
INSERT INTO #Temp SELECT 0, 0
INSERT INTO #Temp SELECT 0, 0
INSERT INTO #Temp SELECT 0, 0
DECLARE @i INT, @j INT
SET @i = 1
SET @j = 1
UPDATE #Temp
SET
@i = @i + 1, @i = i = @i + 1,
@j = @j + 1, @j = @j + 1, j = @j
SELECT * FROM #Temp
DROP TABLE #Temp

Resultset:
i j
3 3
4 5
5 7

Notice how the resultset starts with 3 instead of 2, and i and j don't hold the same values.

Thanks Emtucifor...in SQL 2008 with compound operators, multi-row inserts and initalizing variables when declaring, the code you posted


CREATE TABLE #Temp (i INT, j INT)
INSERT INTO #Temp SELECT 0, 0
INSERT INTO #Temp SELECT 0, 0
INSERT INTO #Temp SELECT 0, 0
DECLARE @i INT, @j INT
SET @i = 1
SET @j = 1
UPDATE #Temp
SET
@i = @i + 1, @i = i = @i + 1,
@j = @j + 1, @j = @j + 1, j = @j
SELECT * FROM #Temp
DROP TABLE #Temp



can be written as the following in 2008 and will save you a couple of lines of code

CREATE TABLE #Temp (i INT, j INT)
INSERT INTO #Temp values(0,0),(0,0),(0,0)
DECLARE @i INT =1, @j INT =1

UPDATE #Temp
SET
@i += 1, @i = i = @i + 1,
@j += 1, @j += 1, j = @j
SELECT * FROM #Temp
DROP TABLE #Temp

Emtucifor, thanks for you excellent example. I use this syntax (@var = column = /expression/) a lot and I have to pay extra attention to it in the future.

Heh... no wonder they were having problems with it... The following construct in the original post from the start of this thread is still incorrect...
______________________________________________
DECLARE @iVal INT

UPDATE CounterTable
SET @iVal = iVal = iVal + 1
WHERE CounterName = 'CaseNumber'

RETURN @iVal
______________________________________________


It should be...
______________________________________________
DECLARE @iVal INT

UPDATE CounterTable
SET @iVal = iVal = @iVal + 1
WHERE CounterName = 'CaseNumber'

RETURN @iVal
______________________________________________

The other thing is that they are missing a few "safeties". To be safe, you must prevent others from inserting/modifying/deleting to or from the table. You must also prevent parallelism. All of that is accomplished as follows...

______________________________________________
DECLARE @iVal INT

UPDATE CounterTable
SET @iVal = iVal = @iVal + 1
FROM CounterTable WITH (TABLOCKX)
WHERE CounterName = 'CaseNumber'
OPTION (MAXDOP 1)

RETURN @iVal
______________________________________________

--Jeff Moden

Yes, Jeff!! Thanks for mentioning the parallelism issue. I don't grasp how it exactly connects, here, but given huge range of problems with it, I'm not surprised.

Actually, I'm really curious to know why it must be an exclusive table lock. Isn't an update an atomic operation? Or is there still a race condition window where someone could read the value after someone else has but before they've updated it?

The use of TABLOCKX suggest to me to use one key-gen table per data table so that high concurrency systems are not harmed.