SQL Server 2008 Inline variable initialization and Compound assignment

By:   |   Updated: 2007-10-15   |   Comments (1)   |   Related: More > TSQL

Master Your Data Environment With DataOps

Free MSSQLTips Webinar: Master Your Data Environment With DataOps

Learn tips and tricks on how to master your data environment with SolarWinds® DataOps solutions, which are designed to help you streamline essential database tasks, database projects, and data-centric application development. Let us show you how to save time on tedious but critical database documentation tasks.

In Sql 2008 (Katmai), a couple of the 'smaller' features that are currently in the latest CTP include inline variable initialization and compound assignment (something you App Dev folks have had for years). So, the following types of code now work in Sql 2008:

  declare @d datetime = getdate(),
    @i int = 1,
    @s varchar(100) = 'test';
  -- Show the values...
  select @d, @i, @s
  -- Increment i, append to s...
  select @i += 1, @s += 'ing';
  -- Show the new values...
  select @i, @s;

These operators also work inline with DML statements and columns...for example, a simple UPDATE statement in a table called 'testTable' with a column called 'testColumn' where you wanted to increment the value of testColumn by 1 could be:

  update testTable set testColumn += 1;
Also would work with other columns...if another column called testColumn2 existed:

  update testTable set testColumn += testColumn2;

I'll be posting other tidbits in Sql 2008 ongoing...enjoy!

get scripts

next tip button

About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips

Article Last Updated: 2007-10-15

Comments For This Article

Thursday, February 18, 2016 - 7:49:41 PM - manu Back To Top (40724)

Please share such tidbits for latest versions. I really like these and is thankful to you for sharing it here.


get free sql tips
agree to terms