HEELPBOOK - Cannot assign a default value to a local variable (SQL Server) ############# Error Message Server: Msg 139, Level 15, State 1, Line 1 Cannot assign a default value to a local variable. ###### Causes 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. This error message can easily be generated using the following DECLARE statement entered in either SQL Server 2000 or SQL Server 2005: DECLARE @CurrentDate DATETIME = GETDATE() Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Here's another sample: DECLARE @Radius FLOAT = 12.5 DECLARE @Diameter FLOAT = PI() * 2 * @Radius Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. One way of knowing if you are connected to SQL Server 2008 is with this error message. If you don't get this error message when you declare a local variable and assigning it a value, then you are connected to SQL Server 2008. Otherwise, you are connected to either SQL Server 2005 or SQL Server 2000. ###### Solution - Workaround To avoid this error, simply separate the declaration of the local variable from the assigning of its initial value. In the case of the first example above, the script will look as follows: DECLARE @CurrentDate DATETIME SET @CurrentDate = GETDATE() In the case of the second example, the script will look as follows: DECLARE @Radius FLOAT DECLARE @Diameter FLOAT SET @Radius = 12.5 SET @Diameter = PI() * 2 * @Radius 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. ############ ARTICLE INFO ############# Article Month: December Article Date: 11/12/2012 Permalink: http://heelpbook.altervista.org/2012/cannot-assign-a-default-value-to-a-local-variable-sql-server/ Source: http://www.sql-server-helper.com/error-messages/msg-139.aspx Language: English View more articles on: http://www.heelpbook.net/ Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832 Follow us on Twitter: https://twitter.com/#!/HeelpBook Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook Follow us on Delicious: http://delicious.com/heelpbook Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20 Google+ : https://plus.google.com/116990277568167008289/posts