Cannot assign a default value to a local variable !


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.

About these ads

2 thoughts on “Cannot assign a default value to a local variable !

  1. Thanks for not pointing out the solution, which is ( using set keyword )
    DECLARE @p0 NVarChar(1000)
    Set @p0 = ‘something’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s