Differences between select and set assignment of variables Stored Procedure SQL 2000

SQL Server assigns values to Defined variables in two ways: Set and select.
The difference between the two methods has been described in detail in SQL Server online books, but many times we
There are many differences between the two methods.

Set instead of select is recommended for SQL Server.
When the expression returns a value and assigns a value to a variable, the set method is recommended.

The following table lists the differences between set and select. Pay special attention to the red part.

	Set	Select
Assign values to multiple variables at the same time	Not Supported	Supported
When the expression returns multiple values	Error	Assign the last returned value to the variable.
The expression does not return a value.	The variable is assigned a null value.	Keep original variable value

The following is an 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 the expression returns multiple values, use set to assign values.

Declare @ ADDR varchar (128)
Set @ ADDR = (select ADDR from chinadba1)
/*
-- The error message is
Server: Message 512, level 16, status 1, Row 2
The subquery returns more than one value. When the subquery follows in = ,! =, <, <=,>,> =, Or use a subquery as an expression.
*/
Go

When the expression returns multiple values, use select to assign values.

Declare @ ADDR varchar (128)
Select @ ADDR = ADDR from chinadba1
Print @ ADDR -- Value of the last ADDR column in The result set
-- Result: addr3
Go

If the expression does not return a value, use set to assign a value.

Declare @ ADDR varchar (128)
Set @ ADDR = 'Initiator'
Set @ ADDR = (select ADDR from chinadba1 where userid = 4)
Print @ ADDR -- Null Value
Go

If the expression does not return a value, use select to assign a value.

Declare @ ADDR varchar (128)
Set @ ADDR = 'Initiator'
Select @ ADDR = ADDR from chinadba1 where userid = 4
Print @ ADDR -- keep Original Value
Go

Note that select can also assign the value of the scalar quantum query to the variable. If the scalar query does not return a value, the variable is set to null.
In this case, it is exactly the same as using set assignment.
The concept of benchmarking quantum queries should be unfamiliar to everyone. For example, it can be explained.

Declare @ ADDR varchar (128)
Set @ ADDR = 'Initiator'
-- Select ADDR from chinadba1 where userid = 4 is a scalar query statement.
Select @ ADDR = (select ADDR from chinadba1 where userid = 4)
Print @ ADDR -- Null Value
Go