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?
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.
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.
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'
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
DECLARE @thresholdDate DATETIME = '2014-11-30' will work in SQL SERVER 2008+..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);
SQL SERVER 2005. It is supported fromSQL SEVER 2008and above