http://www.sqlservercentral.com/blogs/steve_jones/2010/07/21/the-sequence-table-_2D00_-inline-assignment-and-update/

Printed 2014/12/22 08:44PM

The Sequence Table - Inline Assignment and Update

By Steve Jones, 2010/07/21

One thing I learned years ago in C programming was the elegance of using operators and simplifying expressions. This sometimes involved doing two things in a single statement, if it makes sense. For example,

x=y++;


Those of you that have done some programming will recognize this as incrementing y and assigning a value to x at the same time. Well not the same time, but in one line.



I knew that you could some things like this in T-SQL, but it didn’t trigger in my mind when someone asked about building their own sequencing table. This person wanted to update a table with an incremented value and return the value to the calling program without causing any locks/blocks.



Paul White posted this fantastic piece of code that illustrates this:



UPDATE  dbo.GPK

SET     @NewID = next_value = next_value + 1


WHERE   table_name = @table_name
;



In this code the GPK table is being updated, with the next_value column being incremented. At the same time, @NewID, a variable that is an output parameter for a stored proc in this example, is assigned the value from next_value.



The value assigned is the incremented value, so if next_Value contains a 1 before this is run, @NewID will get 2. The increment/update occurs first, with the new value being assigned to the variable.



I think this is really cool, and it’s something I need to remember for future T-SQL problems.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.