Cannot assign a default value to a local variable in SQL

Question

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?

Oophorectomy

It is not possible in SQL SERVER 2005. It is supported from SQL SEVER 2008 and above – Ruffle

In your link to the docs, use the Other Versions drop-down at the top to select SQL 2005 for the correct syntax. – Fawkes

Answer 1

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.

Jezreel

Have you read a question? I want default value (in-line syntax) – Oophorectomy

Have you read the error. Cannot assign a default value to a local variable. Which means your version of SQL does not support it. According to your link Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database. – Irma

For that you have to buy SQL server 2012 – Jezreel

@Irma I kinda see that it's cannot assign, but that still doesn't explains anything. Mention of that earlier versions doesn't support that makes sense. – Oophorectomy

@Jezreel gave you the correct answer, because SQL Server 2005 doesn't support that syntax sugar (in-line syntax) – Displease

Answer 2

You will hit this error

Cannot assign a default value to a local variable

in case you are running SQL Server 2005 or earlier, and you try to declare a variable and assign it a value in one statement.

Something like -

DECLARE @Var Varchar(15) = 'Test'

Message 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable

Variable declaration has been enhanced in SQL Server 2008 and newer versions, and I realized this today when I was deploying a code for multiple systems.

Apparently we also have the flexibility to just use one Declare statement to declare multiple variables -

DECLARE @Var Varchar(15) = 'Test',
        @Char Varchar(10) = 'Test2',
        @Char2 Varchar(10) = 'Test3'

In SQL Server 2005 or earlier, you will need to declare the variable and then assign it a value using Set statement.

Example -

DECLARE @Var Varchar(15) 
SET @Var = 'Test'

Kimberly

Answer 3

The error

Cannot assign a default value to a local variable

occurs if you are using SQL Server 2005 while assigning default value to local variable.

For SQL Server 2005 use this code:

DECLARE @thresholdDate AS DATETIME 

SET @thresholdDate = '2014-11-30'

SELECT @thresholdDate

For SQL Server 2008 and newer you can use this instead:

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

Dede

DECLARE @thresholdDate DATETIME = '2014-11-30' will work in SQL SERVER 2008+.. – Ruffle

Answer 4

DECLARE @SomeVariable AS DATETIME
SET @SomeVariable = '2014-11-30';

//Or if some one like to get last affected record id
DECLARE @SomeVariable AS VARCHAR(100);
SET @SomeVariable = (SELECT SCOPE_IDENTITY() AS ID);

Aron