SQL 分配变量时 SET 还是 SELECT？

SET versus SELECT when assigning variables?

提问by juur

What are the differences between the SETand SELECTstatements when assigning variables in T-SQL?

T-SQL中赋值变量时SET和SELECT语句有什么区别？

回答by OMG Ponies

Quote, which summarizes from this article:

引用，从这篇文章总结：

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)
5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

1. SET 是变量赋值的 ANSI 标准，SELECT 不是。
2. SET 一次只能赋值一个变量，SELECT 可以一次赋值多个。
3. 如果从查询分配，SET 只能分配一个标量值。如果查询返回多个值/行，则 SET 将引发错误。SELECT 会将其中一个值分配给变量并隐藏返回多个值的事实（因此您可能永远不会知道为什么其他地方出了问题 - 解决那个问题很有趣）
4. 从查询分配时，如果没有返回值，则 SET 将分配 NULL，其中 SELECT 根本不会进行分配（因此变量不会从其先前的值更改）
5. 至于速度差异 - SET 和 SELECT 之间没有直接差异。然而，SELECT 能够一次性完成多个任务，这确实使它比 SET 具有轻微的速度优势。

回答by Joe Stefanelli

I believe SETis ANSI standard whereas the SELECTis not. Also note the different behavior of SETvs. SELECTin the example below when a value is not found.

我相信SET是 ANSI 标准，而SELECT不是。另请注意以下示例中未找到值时SETvs.的不同行为SELECT。

declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var is now NULL */

set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty'
select @var /* @var is still equal to 'Joe' */

回答by GorkemHalulu

When writing queries, this difference should be kept in mind :

在编写查询时，应该记住这种区别：

DECLARE @A INT = 2

SELECT  @A = TBL.A
FROM    ( SELECT 1 A ) TBL
WHERE   1 = 2

SELECT  @A
/* @A is 2*/

---------------------------------------------------------------

DECLARE @A INT = 2

SET @A = ( 
            SELECT  TBL.A
            FROM    ( SELECT 1 A) TBL
            WHERE   1 = 2
         )

SELECT  @A
/* @A is null*/

回答by CodingYoshi

Aside from the one being ANSI and speed etc., there is a very important difference that always matters to me; more than ANSI and speed. The number of bugs I have fixed due to this important overlook is large. I look for this during code reviews all the time.

除了 ANSI 和速度等之外，还有一个非常重要的区别对我来说总是很重要；超过ANSI和速度。由于这个重要的忽视，我修复的错误数量很多。我一直在代码期间寻找这个。

-- Arrange
create table Employee (EmployeeId int);
insert into dbo.Employee values (1);
insert into dbo.Employee values (2);
insert into dbo.Employee values (3);

-- Act
declare @employeeId int;
select @employeeId = e.EmployeeId from dbo.Employee e;

-- Assert
-- This will print 3, the last EmployeeId from the query (an arbitrary value)
-- Almost always, this is not what the developer was intending. 
print @employeeId; 

Almost always, that is not what the developer is intending. In the above, the query is straight forward but I have seen queries that are quite complex and figuring out whether it will return a single value or not, is not trivial. The query is often more complex than this and by chance it has been returning single value. During developer testing all is fine. But this is like a ticking bomb and will cause issues when the query returns multiple results. Why? Because it will simply assign the last value to the variable.

几乎总是，这不是开发人员的意图。在上面，查询是直截了当的，但我已经看到非常复杂的查询，并且弄清楚它是否会返回单个值，这并非易事。查询通常比这更复杂，并且偶然地它一直返回单个值。在开发人员测试期间一切正常。但这就像一个定时炸弹，当查询返回多个结果时会导致问题。为什么？因为它只会将最后一个值分配给变量。

Now let's try the same thing with SET:

现在让我们尝试同样的事情SET：

 -- Act
 set @employeeId = (select e.EmployeeId from dbo.Employee e);

You will receive an error:

你会收到一个错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。

That is amazing and very important because why would you want to assign some trivial "last item in result" to the @employeeId. With selectyou will never get any error and you will spend minutes, hours debugging.

这很神奇也很重要，因为您为什么要将一些微不足道的“结果中的最后一项”分配给@employeeId. 有了它，select您将永远不会出错，而且您将花费数分钟、数小时进行调试。

Perhaps, you are looking for a single Id and SETwill force you to fix your query. Thus you may do something like:

也许，您正在寻找一个 Id 并SET会迫使您修复您的查询。因此，您可以执行以下操作：

-- Act
-- Notice the where clause
set @employeeId = (select e.EmployeeId from dbo.Employee e where e.EmployeeId = 1);
print @employeeId;

Cleanup

清理

drop table Employee;

---

---

In conclusion, use:

总之，使用：

• SET: When you want to assign a single value to a variable and your variable is for a single value.
• SELECT: When you want to assign multiple values to a variable. The variable may be a table, temp table or table variable etc.

• SET：当您想为变量分配单个值并且您的变量用于单个值时。
• SELECT: 当您想为一个变量分配多个值时。变量可以是表、临时表或表变量等。