Differences between Select and SET on variable assignments in SQL Server stored procedures

Transferred from: http://www.cnblogs.com/micheng11/archive/2008/07/08/1237905.html

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 TABLE Chinadba1 (
UserID int,
Addr varchar (128)
)
Go
Insert into CHINADBA1 (USERID,ADDR) VALUES (1, ' ADDR1 ')
Insert into CHINADBA1 (USERID,ADDR) VALUES (2, ' ADDR2 ')
Insert into CHINADBA1 (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 (128)
Select @addr = addr from Chinadba1
Print @addr-The value of the last addr column in the result set
--Result: ADDR3
Go

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

DECLARE @addr varchar (128)
Set @addr = ' initial value '
Set @addr = (select addr from chinadba1 where UserID = 4)
Print @addr--null value
Go

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

DECLARE @addr varchar (128)
Set @addr = ' initial value '
Select @addr = addr from chinadba1 where UserID = 4
Print @addr--Keep the original value
Go

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 (128)
Set @addr = ' initial value '
--select addr from chinadba1 where UserID = 4 for scalar quantum query statements
Select @addr = (select addr from chinadba1 where UserID = 4)
Print @addr--null value
Go

Differences between Select and SET on variable assignments in SQL Server stored procedures