In this blog post, let’s learn about the error message “139 – Cannot assign a default value to a local variable.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
139 – Cannot assign a default value to a local variable.
Reason for the Error
This error occurs when attempting to assign a default value to a local variable in SQL Server. It indicates that the syntax of the statement is incorrect and cannot be parsed by SQL Server.
Here’s an example of the error:
DECLARE @myVar INT = DEFAULT;
In this example, we’re attempting to assign the default value to a local variable, which is not allowed in SQL Server. This statement will produce the following error message:
Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable.
Solution
To fix this error, remove the “DEFAULT” keyword from the assignment statement and replace it with an appropriate default value. Here’s an example of a correct syntax:
This will assign the value of 0 to the @myVar variable.
It’s important to note that this error message only occurs in SQL Server 2008 and earlier versions. In later versions of SQL Server, default values can be assigned to local variables using the “SET” statement. Here’s an example:
DECLARE @myVar INT; SET @myVar = DEFAULT;
In this example, we declare the @myVar variable and then assign it a default value using the “SET” statement. This syntax is allowed in SQL Server 2012 and later versions.
In summary, the SQL Server error message 139 occurs when attempting to assign a default value to a local variable using the “DEFAULT” keyword in SQL Server 2008 and earlier versions. To fix the error, remove the “DEFAULT” keyword and replace it with an appropriate default value. In later versions of SQL Server, default values can be assigned using the “SET” statement.
