Add-Equals operator

Shortcuts & tips from an Impatient DBA

This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are the little things that I do as part of my daily work that make me more efficient.

 

Concatenating strings

You’ve written code that builds out a string. The most common reason I do it is when I’m writing dynamic SQL for my automation code. In fact, you’re probably already thinking of the code you wrote where you did something like this:

DECLARE @sql varchar(4000);
SET @sql = 'SELECT Col001 FROM [table] ';
SET @sql = @sql + ' WHERE 1=2;';  -- Concatenating @sql manually
SELECT @sql;

I’ve been writing code like that for a decade….which is the problem. Ever since SQL Server 2008, I’ve been wasting precious keystrokes.

 

There’s a better way

SQL Server 2008 introduced an Add-Equals operator (+=) to T-SQL. Somehow, this hidden gem slipped past me. That whole “@sql = @sql +” nonsense can be made a lot easier to read.

DECLARE @sql varchar(4000);
SET @sql =  'SELECT Col001 FROM [table] ';
SET @sql += ' WHERE 1=2;';  -- Concatenation operator
SELECT @sql;

This is logically equivalent to the first version of the code, but I find it makes for more readable code. It just looks cleaner.

For those of us who are lazy looking to maximize efficiency, this could save a whole lot of key strokes.

 

But wait, there’s more

If you call now, you can have not one, but TWO ShamWows!

DECLARE @shamwow int = 0;
SET @shamwow += 1;
SET @shamwow += 1;
SELECT @shamwow AS ShamWow;

Yup, the Add-Equals operator works with both strings and numbers.

So the next time you talk yourself into writing a loop or a cursor, and you implement a counter, you can save some keystrokes. That makes it efficient, right?

DECLARE @count int = 3;
PRINT 'Numbers less than 100 that are also multiples of three.'
WHILE @count < 100
BEGIN
 PRINT @Count;
 SET @Count += 3;
END;