SQL Server 2008 – Assign a default value to a local variable

I just discovered today, that SQL Server 2008 allows you to assign a default value to a local variable. I was not aware that this functionality had been added until I had a stored procedure I was working with on my local SQL 2008 database and tried to execute it on the shared development database, which is still running SQL 2005 and I received the following error:

Msg 139, Level 15, State 1, Procedure apGetRollCallData, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Procedure apGetRollCallData, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure apGetRollCallData, Line 79
Must declare the scalar variable "@StartRange".
Msg 137, Level 15, State 2, Procedure apGetRollCallData, Line 89
Must declare the scalar variable "@StartRange".

  

I had the following declarations in my procedure that were causing the issue.

DECLARE @StartRange DATETIME = @ViewDate
DECLARE @EndRange DATETIME = @ViewDate

Once I changed it back to the following, it worked just fine in SQL Server 2005.

DECLARE @StartRange DATETIME
SET @StartRange = @ViewDate
DECLARE @EndRange DATETIME
SET @EndRange = @ViewDate

From a .NET developers point of view, I like having the ability to set a default value for a local variable in TSQL just like I can do in my programming language of choice.

 

Very nice. Thanks Microsoft.