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 = EmployeeID FROM (SELECT EmployeeID FROM HumanResources.Employee UNION ALL SELECT EmployeeID 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 EmployeeID 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 AdventureWorks SET compatibility_level = 90; GO USE AdventureWorks; GO DECLARE @v int; SELECT @v = EmployeeID FROM HumanResources.Employee UNION ALL SELECT @v = EmployeeID FROM HumanResources.EmployeeAddress; SELECT @v;