Variable assignment is not allowed in a statement containing a top-level UNION operator. Error 10734 is returned. To resolve the error, rewrite the query as shown in the following example. Copy DECLARE @v int; SELECT @v = BusinessEntityID FROM (SELECT BusinessEntityID FROM HumanResources.Employee UNION ALL SELECT BusinessEntityID FROM HumanResources.EmployeeAddress) AS Test; SELECT @v; [Possibility of impact] Low
Variable assignment is allowed in a statement containing a top-level UNION operator, but returns unexpected results. For example, in the following statements, local variable @v is assigned the value of the column BusinessEntityID from the union of two tables. By definition, when the SELECT statement returns more than one value, the variable is assigned the last value that is returned. In this case, the variable is correctly assigned the last value, however, the result set of the SELECT UNION statement is also returned. Copy ALTER DATABASE AdventureWorks2012 SET compatibility_level = 90; GO USE AdventureWorks2012; GO DECLARE @v int; SELECT @v = BusinessEntityID FROM HumanResources.Employee UNION ALL SELECT @v = BusinessEntityID FROM HumanResources.EmployeeAddress; SELECT @v;