Variable assignment is not allowed in a statement

[Laxminarayana K]

Hi Guys,

 

Today, I got one new error message in SQL Server 2008 R2.

 

Problem:

Eg:

select @n=name from sysobjects

union

select @n=name from sys.objects

 

Like above, we are having similar code in our environement. It was working fine in SQL 2005. But, reporting below error message in SQL 2008 R2.

Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.

Note: It was highlighed in Upgrade Advisor

Solution:

Code should be changed as below

 

select @n=name from

(select name from sysobjects

union

select name from sys.objects) as x

 

 

Regards,

Laxminarayana