Cannot assign a default value to a local variable in SQL

sql sql-server sql-server-2005 default-value declare
Vladimirs picture Vladimirs · Nov 21, 2014 · Viewed 44.2k times · Source

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?

Answer

Veera picture Veera · Nov 21, 2014

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.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More