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
|
Differences between Select and SET on variable assignments in SQL Server stored procedures