Subscribe to this blog
Briefcase
Print
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.
No comments.