Search code examples
sqlsql-serversql-server-2005default-valuedeclare

Cannot assign a default value to a local variable in SQL

I am trying to declare local variable like:

DECLARE @thresholdDate DATETIME = '2014-11-30'

And I am getting error:

Cannot assign a default value to a local variable.

As per documentation:

DECLARE @find varchar(30); 
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%'; 
*/

What I am doing wrong?

Solution

  • Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered.

    Solution 1: (Use SET)

    DECLARE @thresholdDate DATETIME 
set @thresholdDate = '2014-11-30'

    For more details about the error : http://www.sql-server-helper.com/error-messages/msg-139.aspx

    Solution 2: (Upgrade)

    Another way of avoiding this error, which is a little bit a far-fetched solution, is to upgrade to SQL Server 2008. SQL Server 2008 now allows the assigning of a value to a variable in the DECLARE statement.