Differences between SQL Server select and set assignments

There are two ways to assign values to defined variables in--sql server, namely SET and SELECT

--When an expression returns a value and assigns a value to a variable, it is recommended to use the SET method

(1) Select can assign values to multiple variables in a single statement, while set assigns only one variable at a time  

Select @para_1 = ' 1 ', @para_2 = ' 2 ';

and set to achieve the same effect, you need:

Set @para_1 = ' 1 '

Set @para_2 = ' 2 '

(2) When an expression returns multiple values, using set will make an error, and select will take the last value

Hypothesis: Table_1 table has multiple sex records

Select @para_1 = Sex from Table_1--will take the last value

Set @para_1 = Sex from Table_1--will error

(3) When the expression has no return value, set the value of the variable to null, with the select variable will remain the original value

Assumption: Table_1 table is empty

Set @para_1 = ' initial value '

Select @para_1 = Sex from Table_1-@para_1 "initial value" at this time

Set @para_1 = Sex from table_1-@para_1 is null at this time

(4) When using a scalar subquery, if there is no return value, set and select are set to NULL

Assumption: Table_1 table is empty

Set @para_1 = ' initial value '

Select @para_1 = (select sex from Table_1)-@para_1 is null at this time

Set @para_1 = (select sex from Table_1)-@para_1 is null at this time

Differences between SQL Server select and set assignments