http://www.sqlservercentral.com/blogs/sqlsailorcom/2012/09/27/cannot-assign-a-default-value-to-a-local-variable-/

Printed 2014/10/23 07:22AM

Cannot assign a default value to a local variable !

By AnupWarrier, 2012/09/27

You will hit this error Cannot assign a default value to a local variable in case you are running SQL 2005/below,and you try to declare a variable and assign it a value in one statement.

Something like  -

DECLARE @Var Varchar(15) = 'Test'

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

Variable declaration has been enhanced in SQL2008/above and I realized this today when I was deploying a code for multiple systems.

The same declaration mentioned above will work great for 2008/above

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'

These are indeed “feel good to have” enhancements !

[Update Added on 10/24/202]

In SQL2005 or below you will need to declare the variable and then assign it a value using Set statement.

Example -

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

[Update]

Thanks for reading.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.