DECLARE and SET Variables in one line SQL Server 2008, R2, 2012, 2014

How many times do we as SQL developers have to declare and set variables?  I recon that this occurs quite frequently, perhaps a million times a day…well, maybe not that many but it FEELS like it sometimes.  Prior to SQL Server 2008, we were forced to declare the variable in one statement and then set it in a following statement.  We got used to it but it was a constant annoyance for those of us to have better things to do then fiddle with SET statements every other line.

With SQL Server 2008, 2012 and above, we can now declare and set variables in a single statement.

-- Old clunky way of doing things prior to SQL Server 2008
DECLARE @myVar VARCHAR(100);
SET @myVar = 'Some Value';

-- New, amazing way of declaring variables
DECLARE @myVar VARCHAR(100) = 'Some Value';

We can see above the old, nasty way we are all familiar with.  Then we see the amazingly simple and efficient way we can do it now with SQL Server 2008 and above.  I bet we save on average of 10-12 keystrokes.  Multiplying that times the million times we have to do this a day and you’ll see we end up with quite a significant savings of work.  Your life just got a bit better!

Power on SQL Devs!

Here is the full technical reference for declaring a setting variables in SQL Server 2014.

Valid for SQL Server 2008, 2008 R2, 2012, 2014.

Leave a Reply

Your email address will not be published. Required fields are marked *