SQL Server 2008 Variable Declaration

Another day a developer approached me stating that the code works in local development server but it does not work when deployed to QA. I know that QA server is running SQL Server 2005. I heard that lately the development team was getting new workstations with new set up. So I was sure that they were running something other than SQL server 2005. I asked the developer if the sql server running on the workstation was SQL 2008. The answer was yes and I then asked how variables were declared and assigned the valued. It was just the first thing that came to my mind. And I was right.

So, starting with SQL Server 2008 Microsoft made improvements on declaring variables and assigning values to them. Here is an example for SQL Server 2008 and newer versions.

DECLARE @Startdate DATETIME = GETDATE()

DECLARE @AccountID INT = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

However if you run the above statement in SQL 2005 or earlier you will get an error message. The only way above statement will work on SQL 2005 and earlier versions is;

DECLARE @Startdate DATETIME

DECLARE @AccountID INT

SELECT @Startdate = GETDATE(),

@AccountID = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

As you see it is less of t-sql code for SQL Server 2008 and newer versions. There is some more enhancements but I will cover that in my next blog.

HTH,

Bulent