将值分配给SQL Server变量时应选择什么：SET与SELECT T-SQL语句

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

SQL Server在T-SQL中为我们提供了两种方法，可为先前创建的本地SQL变量分配值。 第一种方法是SET语句，这是ANSI标准语句，通常用于变量值分配。 第二条语句是SELECT语句。 SELECT语句除了主要用于形成用于从数据库表或SQL Server中的多个表检索数据的逻辑外，还可以用于将值直接分配给先前创建的局部变量或从变量中分配值，视图或表格。

Although both T-SQL statements fulfill the SQL variable value assignment task, there is a number of differences between the SET and SELECT statements that may lead you to choose one of them in specific circumstances, over the other. In this article, we will describe, in detail, when and why to choose between the SET and SELECT T-SQL statements while assigning a value to a variable.

尽管两个T-SQL语句都完成了SQL变量值分配任务，但SET和SELECT语句之间存在许多差异，这可能导致您在特定情况下选择其中一个，而不是另一个。 在本文中，我们将详细描述何时以及为什么在为变量赋值的同时在SET和SELECT T-SQL语句之间进行选择。

We will start with creating a new table and fill it with few records for our demo. This can be achieved using the below script:

我们将从创建一个新表开始，并为我们的演示填充几条记录。 可以使用以下脚本来实现：

USE SQLShackDemo
GO
CREATE TABLE SetVsSelectDemo
(
  ID  INT IDENTITY (1,1) PRIMARY KEY,
  Name NVARCHAR (50),
  GroupNumber INT,
  Grade INT
)
GO
INSERT INTO SetVsSelectDemo VALUES ('Adel',1,350)
INSERT INTO SetVsSelectDemo VALUES ('Faisal',1,240)
INSERT INTO SetVsSelectDemo VALUES ('Huda',2,180)
INSERT INTO SetVsSelectDemo VALUES ('Zaid',2,170)
INSERT INTO SetVsSelectDemo VALUES ('Zaina',3,290)
INSERT INTO SetVsSelectDemo VALUES ('John',4,400)
INSERT INTO SetVsSelectDemo VALUES ('Igor',4,375)

The inserted data can be checked using the following SELECT statement:

可以使用以下SELECT语句检查插入的数据：

SELECT * FROM SetVsSelectDemo

And the data will be shown as below:

数据将显示如下：

If we manage to assign a scalar value for the SQL variable that is previously defined using the DECLARE statement, both the SET and SELECT statements will achieve the target in the same way. The below SET statement is used to assign the @EmpName1 variable with the scalar “Ali” value:

如果我们设法为先前使用DECLARE语句定义SQL变量分配标量值，则SET和SELECT语句将以相同的方式实现目标。 下面的SET语句用于将标量“ Ali”值分配给@ EmpName1变量：

DECLARE @EmpName1 NVARCHAR(50)
SET @EmpName1 = 'Ali'
PRINT @EmpName1 
GO

In the same way, the below SELECT statement can be used to assign the @EmpName2 variable with the scalar “Ali” value:

以相同的方式，下面的SELECT语句可用于为标量“ Ali”值分配@ EmpName2变量：

DECLARE @EmpName2 NVARCHAR(50)
SELECT @EmpName2 = 'Ali'
PRINT @EmpName2
GO

The assigned values for the variables in the previous queries will be printed in the Messages tab as shown below:

先前查询中变量的分配值将显示在“消息”选项卡中，如下所示：

SQL Server allows us to assign value for a SQL variable from a database table or view. The below query is used to assign the @EmpName variable the Name column value of the third group members from the SetVsSelectDemo table using the SET statement:

SQL Server允许我们从数据库表或视图中为SQL变量分配值。 下面的查询用于使用SET语句从SetVsSelectDemo表中为@EmpName变量分配第三组成员的Name列值：

DECLARE @EmpName NVARCHAR(50)
SET @EmpName = (SELECT [Name] FROM SetVsSelectDemo WHERE GroupNumber = 3)
PRINT @EmpName
GO

The SELECT statement can be also used to perform the same assignment task in a different way as shown below:

SELECT语句还可用于以不同的方式执行相同的分配任务，如下所示：

DECLARE @EmpName NVARCHAR(50)
SELECT @EmpName =  [Name] FROM SetVsSelectDemo WHERE GroupNumber = 3
PRINT @EmpName
GO

The results of the previous two queries will be displayed in the Messages tab as shown below:

前两个查询的结果将显示在“消息”选项卡中，如下所示：

Until this point, you can see that both the SET and SELECT statements can perform the variable value assignment task in the same way and differ from the code side only.

到目前为止，您可以看到SET和SELECT语句都可以以相同的方式执行变量值分配任务，并且仅与代码端不同。

多个SQL变量 (Multiple SQL Variables )

Assume that we need to assign values to multiple variables at one shot. The SET statement can assign value to one variable at a time; this means that, if we need to assign values for two variables, we need to write two SET statements. In the below example, each variable requires a separate SET statement to assign it scalar value, before printing it:

假设我们需要一次将值分配给多个变量。 SET语句可以一次将值赋给一个变量。 这意味着，如果我们需要为两个变量分配值，则需要编写两个SET语句。 在下面的示例中，每个变量在打印之前都需要一个单独的SET语句为其分配标量值：

DECLARE @EmpName1 NVARCHAR(50) , @EmpName2 NVARCHAR(50) 
SET @EmpName1 = 'Ali'
SET @EmpName2 = 'Fadi'
PRINT @EmpName1 
PRINT @EmpName2
GO

On the other hand, the SELECT statement can be used to assign values to the previously defined multiple SQL variables using one SELECT statement. The below SELECT statement can be easily used to assign scalar values to the two variables using one SELECT statement before printing it:

另一方面，可以使用一个SELECT语句使用SELECT语句将值分配给先前定义的多个SQL变量。 下面的SELECT语句可以很容易地用于在打印前使用一个SELECT语句将标量值分配给两个变量：

DECLARE @EmpName1 NVARCHAR(50) , @EmpName2 NVARCHAR(50) 
SELECT @EmpName1 = 'Ali', @EmpName2 = 'Fadi'
PRINT @EmpName1 
PRINT @EmpName2
GO

You can see from the printed result below, that both statements achieve the same task, with the SELECT statement better than the SET statement when trying to assign values to multiple variables due to code simplicity:

您可以从下面的打印结果中看到，由于代码的简单性，当试图将值分配给多个变量时，这两个语句完成了相同的任务，其中SELECT语句比SET语句更好：

Again, if we try to assign values from database table to multiple variables, it requires us SET statements equal to the number of variables. In our example, we need two SET statements to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade variables as shown in the script below:

同样，如果我们尝试将数据库表中的值分配给多个变量，则需要我们的SET语句等于变量的数量。 在我们的示例中，我们需要两个SET语句才能将SetVsSelectDemo表中的值分配给@EmpName和@EmpGrade变量，如下面的脚本所示：

DECLARE @EmpName NVARCHAR(50), @EmpGrade INT
SET @EmpName = (SELECT [Name] FROM SetVsSelectDemo WHERE GroupNumber = 3)
SET @EmpGrade = (SELECT [Grade] FROM SetVsSelectDemo WHERE GroupNumber = 3)
PRINT @EmpName
PRINT @EmpGrade
GO

On the other hand, only one SELECT statement can be used to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade SQL variables, using simpler query as shown clearly below:

另一方面，只能使用一个SELECT语句使用以下更简单的查询将SetVsSelectDemo表中的值分配给@EmpName和@EmpGrade SQL变量：

DECLARE @EmpName NVARCHAR(50), @EmpGrade INT
SELECT @EmpName=[Name] , @EmpGrade =[Grade] FROM SetVsSelectDemo WHERE GroupNumber = 3
PRINT @EmpName
PRINT @EmpGrade
GO

It is obvious from the previous two queries that the query that is using the SELECT statement is more efficient than the one using the SET statement when assigning values to multiple variables at the same time, due to the fact that, the SET statement can only assign one variable at a time. The similar results of the previous two queries that are printed in the Messages tab will be like the below in our case:

从前两个查询中可以明显看出，将SELECT语句同时分配给多个变量时，使用SELECT语句的查询比使用SET语句的查询更有效。一次一个变量。 在我们的案例中，“消息”选项卡中打印的前两个查询的结果类似：

多个值 (Multiple values)

The second point, in which the difference between assigning values to the SQL variables using the SELECT or SET statements appears, is when the result set of the subquery query that is used to assign a value to the variable returns more than one value. In this case, the SET statement will return an error as it accepts only one scalar value from the subquery to assign it to the variable, while the SELECT statement accepts that situation, in which the subquery will return multiple values, without raising any error. You will not, though, have any control on which value will be assigned to the variable, where the last value returned from the subquery will be assigned to the variable.

第二点是使用SELECT或SET语句为SQL变量分配值之间的差异，这是当用于将变量分配值的子查询查询的结果集返回多个值时。 在这种情况下，SET语句将返回错误，因为它仅接受来自子查询的一个标量值以将其分配给变量，而SELECT语句接受这种情况，即子查询将返回多个值而不会引发任何错误。 但是，您将无法控制将哪个值分配给变量，从子查询返回的最后一个值将分配给变量。

Assume that we need to assign the Name value of the second group from the previously created SetVsSelectDemo table to the @EmpName SQL variable. Recall that the second group on that table contains two records in the result set as shown below:

假设我们需要将先前创建的SetVsSelectDemo表中第二个组的Name值分配给@EmpName SQL变量。 回想一下，该表的第二组在结果集中包含两个记录，如下所示：

The script that is used to assign the @EmpName variable value from the SetVsSelectDemo table using the SET and SELECT statements will be like:

使用SET和SELECT语句从SetVsSelectDemo表分配@EmpName变量值的脚本将类似于：

DECLARE @EmpName NVARCHAR(50)
SET @EmpName = (SELECT [Name] FROM SetVsSelectDemo WHERE GroupNumber = 2)
PRINT @EmpName
GO
 
DECLARE @EmpName NVARCHAR(50)
SELECT @EmpName =  [Name] FROM SetVsSelectDemo WHERE GroupNumber = 2
PRINT @EmpName
GO

Due to the fact that, the subquery statement returned two records, assigning value to the @EmpName SQL variable using the SET statement will fail, as the SET statement can assign only single value to the variables. This is not the case when assigning value to the @EmpName variable using the SELECT statement that will succeed with no error, assigning the name from the second returned record, which is “Zaid”, to the variable as shown in the result messages below:

由于以下事实，子查询语句返回了两条记录，使用SET语句将值分配给@EmpName SQL变量将失败，因为SET语句只能将单个值分配给变量。 当使用SELECT语句为@EmpName变量赋值时会出现这种情况，而不会出现任何错误，而是将返回的第二个记录的名称“ Zaid”分配给变量，情况并非如此，如以下结果消息所示：

We can learn from the previous result that, when you expect that the subquery will return more than one value, it is better to use the SET statement to assign value to the variable by implementing a proper error handling mechanism, rather than using the SELECT statement that will assign the last returned value to the SQL variable, with no error returned to warn us that the subquery returned multiple values.

我们可以从先前的结果中了解到，当您期望子查询返回多个值时，最好通过实现适当的错误处理机制，而不是使用SELECT语句，使用SET语句为变量赋值。会将最后返回的值分配给SQL变量，没有返回错误，警告我们子查询返回了多个值。

不分配值 (Assign no value)

Another difference between assigning values to the SQL variables using the SET and SELECT statements, is when the subquery that is used to assign a value to the variable return no value. If the previously declared variable has no initial value, both the SET and SELECT statement will act in the same way, assigning NULL value to that variable.

使用SET和SELECT语句为SQL变量赋值的另一个区别是，用于给变量赋值的子查询没有返回值。 如果先前声明的变量没有初始值，则SET和SELECT语句将以相同的方式起作用，将NULL值分配给该变量。

Assume that we need to assign the @EmpName variable, with no initial value, the Name of the fifth group from the SetVsSelectDemo table. Recall that this table has no records that belong to the fifth group as shown below:

假设我们需要分配@EmpName变量（不带初始值），即SetVsSelectDemo表中第五个组的名称。 回想一下，该表没有属于第五组的记录，如下所示：

The script that is used to assign the value to the @EmpName variable from the SetVsSelectDemo table will be like:

用于将值分配给SetVsSelectDemo表中的@EmpName变量的脚本将类似于：

DECLARE @EmpName NVARCHAR(50)
SET @EmpName = (SELECT [Name] FROM SetVsSelectDemo WHERE GroupNumber = 5)
SELECT @EmpName AS SET_Name
GO
 
DECLARE @EmpName NVARCHAR(50)
SELECT @EmpName =  [Name] FROM SetVsSelectDemo WHERE GroupNumber = 5
SELECT @EmpName AS SELECT_Name
GO

Having no initial value for the @EmpName variable, and no value returned from the subquery, a NULL value will be assigned to that variable in both cases as shown clearly in the result message below:

由于@EmpName变量没有初始值，并且子查询没有返回值，因此在两种情况下都将为该变量分配一个NULL值，如以下结果消息中清楚所示：

If the previously declared SQL variable has an initial value, and the subquery that is used to assign a value to the variable returns no value, the SET and SELECT statement will behave in different ways. In this case, the SET statement will override the initial value of the variable and return the NULL value. On the contrary, the SELECT statement will not override the initial value of the variable and will return it, if no value is returned from the assigning subquery.

如果先前声明SQL变量具有初始值，并且用于向变量分配值的子查询不返回任何值，则SET和SELECT语句的行为将有所不同。 在这种情况下，SET语句将覆盖变量的初始值并返回NULL值。 相反，如果分配子查询未返回任何值，则SELECT语句将不会覆盖该变量的初始值，并将其返回。

If we arrange again to assign the @EmpName variable, the Name of the fifth group from the SetVsSelectDemo table, recalling that this table has no records that belong to the fifth group, but this time, after setting an initial value for the @EmpName SQL variable during the variable declaration, using the SET and SELECT statements, as shown in the script below:

如果我们再次安排分配@EmpName变量，则是SetVsSelectDemo表中第五组的名称，回想一下该表没有属于第五组的记录，但是这次是在为@EmpName SQL设置初始值之后使用SET和SELECT语句在变量声明期间定义变量，如以下脚本所示：

DECLARE @EmpName NVARCHAR(50)='Sanya'
SET @EmpName = (SELECT [Name] FROM SetVsSelectDemo WHERE GroupNumber = 5)
SELECT @EmpName AS SET_Name
GO
 
DECLARE @EmpName NVARCHAR(50)='Sanya'
SELECT @EmpName =  [Name] FROM SetVsSelectDemo WHERE GroupNumber = 5
SELECT @EmpName AS SELECT_Name
GO

Taking into consideration that the assigning subquery retuned no value, the query that used the SET statement to assign value to the SQL variable will override the initial value of the variable, returning NULL value, while the query that used the SELECT statement to assign value to the variable will keep the initial value with no change as no value is returned from the subquery, as shown clearly in the results below:

考虑到分配子查询未调整任何值，使用SET语句将值分配给SQL变量的查询将覆盖变量的初始值，返回NULL值，而使用SELECT语句将值分配给该查询的查询该变量将保持初始值不变，因为子查询没有返回任何值，如以下结果所示：

结论 (Conclusion)

SQL Server provides us with two main methods that are used to assign values to the SQL variables. In most cases, both the SET and SELECT statements fulfill the variable value assignment task with no issue. In some situations, you may prefer using one over the other, such as:

SQL Server为我们提供了两种主要方法，用于为SQL变量赋值。 在大多数情况下，SET和SELECT语句都可以毫无问题地完成变量值分配任务。 在某些情况下，您可能更喜欢使用一种，例如：

• If you manage to assign values to multiple variables directly or from a database table, it is better to use the SELECT statement, that requires one statement only, over the SET statement due to coding simplicity
  如果您设法直接或从数据库表中将值分配给多个变量，则由于编码简单，最好使用SELECT语句，而该语句只需要一个语句，而不是SET语句
• If you are following the ANSI standard for code migration purposes, use the SET statement for SQL variables values assignment, as the SELECT statement does not follow the ANSI standard
  如果出于代码迁移目的而遵循ANSI标准，请使用SET语句进行SQL变量值分配，因为SELECT语句不遵循ANSI标准
• If the assigning subquery returns multiple values, using the SET statement to assign value to a variable will raise an error as it only accepts a single value, where the SELECT statement will assign the last returned value from the subquery to the variable, with no control from your side
  如果分配子查询返回多个值，则使用SET语句将值分配给变量将引发错误，因为它仅接受单个值，其中SELECT语句将子查询中最后返回的值分配给变量，而没有控制权从你那边
• If the assigning subquery returns no value, the SET statement will override the variable initial value to NULL, while the SELECT statement will not override its initial value
  如果分配子查询不返回任何值，则SET语句将变量初始值覆盖为NULL，而SELECT语句将不覆盖其初始值。

翻译自: https://www.sqlshack.com/what-to-choose-when-assigning-values-to-sql-server-variables-set-vs-select-t-sql-statements/