Wednesday, April 18, 2012

UPDATE a Column While Simultaneously Setting a Local Variable

I saw an interesting pattern in a Microsoft-supplied stored procedure today. They update a column and write a local variable at the same time.

Here is an illustration of the technique.

Code:
USE tempdb; 
SET NOCOUNT ON;
GO

CREATE TABLE test1 (
  
ColId       INT             IDENTITY,
  
ColValue1   VARCHAR(20),
  
ColValue2   VARCHAR(20)
);


INSERT INTO test1 (ColValue1)

VALUES ('Col1-Initial'); 

INSERT INTO test1 (ColValue2)
VALUES ('Col2-Initial');

DECLARE @value VARCHAR(20);


SELECT ColValue1, ColValue2 FROM test1;

UPDATE test1 
SET @value = ColValue2 = ColValue1 + '-Updated'
WHERE ColId = 1;

SELECT @value AS '@value';

SELECT ColValue1, ColValue2 FROM test1;

Results:

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         NULL
NULL                 Col2-Initial

@value
--------------------
Col1-Initial-Updated

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         Col1-Initial-Updated
NULL                 Col2-Initial