The difference between select and SET pairs of variables in SQL Server stored procedure transfer from Theo

There are two ways to assign values to variables that are already defined in SQL Server, SET and SELECT, respectively.
The differences between these two approaches are explained in more detail in SQL Server Books Online, but many times we
Did not notice, in fact, these two ways still have a lot of differences.

SQL Server recommends using SET instead of SELECT to assign a value to a variable.
The SET method is recommended when an expression returns a value and assigns a variable to it.

The following table lists the differences between SET and SELECT. Please pay special attention to the red section.

	Set	Select
Simultaneously assign values to multiple variables simultaneously	Not supported	Support
When an expression returns multiple values	Error	Assigns the last value returned to the variable
Expression does not return a value	variable is assigned a null value	The variable holds the original value

Here's a specific example to illustrate the problem:

Create TableCHINADBA1 (useridint, addrvarchar( -))GoInsert  intoCHINADBA1 (USERID,ADDR)Values(1,'ADDR1')Insert  intoCHINADBA1 (USERID,ADDR)Values(2,'ADDR2')Insert  intoCHINADBA1 (USERID,ADDR)Values(3,'ADDR3')Go

When an expression returns more than one value, use SET to assign a value

DECLARE @addr varchar (128)
Set @addr = (select addr from Chinadba1)
/*
--The error message is
Server: Msg 512, Level 16, State 1, line 2
The subquery returns more than one value. This is not allowed when subqueries are followed by =,! =, <, <=, >, >=, or when the subquery is used as an expression.
*/
Go

When an expression returns multiple values, use SELECT to assign a value

Declare @addr varchar (+) Select @addr =  from CHINADBA1 Print @addr -- the value of the last addr column in the result set -- results: ADDR3 Go

When an expression does not return a value, use SET to assign a value

Declare @addr varchar( -)Set @addr = 'Initial value'Set @addr =(SelectAddr fromChinadba1whereUserid= 4 )Print @addr --Null valueGo

Use SELECT to assign a value when an expression does not return a value

Declare @addr varchar( -)Set @addr = 'Initial value'Select @addr =Addr fromChinadba1whereUserid= 4Print @addr --keep the original valueGo

It is important to note that SELECT can also assign the value of the scalar subquery to a variable, and if the scalar subquery does not return a value, the variable is set to a null value.
This is exactly the same as using SET assignment
The concept of scalar quantum query everyone should be unfamiliar, for example, can explain

Declare @addr varchar( -)Set @addr = 'Initial value'--Select addr from chinadba1 where UserID = 4 for scalar quantum query statementsSelect @addr =(SelectAddr fromChinadba1whereUserid= 4)Print @addr --Null valueGo

The difference between select and SET pairs of variables in SQL Server stored procedure transfer from Theo