Using SELECT and SET statement for variable assignments is a common practice in SQL Server. I did not pay more attention to SELECT vs SET difference, until I met with surprising logical errors. In production code using SELECT over SET, produced unanticipated results. Both are used interchangeably for variable assignment. Importantly, understanding when to use SELECT over SET and vice versa.
In this tech-recipes post, an understanding of the difference between SELECT and SET statement. Moreover, walking through various examples involving both options. As a result, this will help you to come up with good practice and use appropriate option while variable assignment in your code as per need.
Points to Ponder – SELECT vs SET
1.SELECT can assign values to multiple variables in a single query. While SET is used for single variable assignment.
2.SET is ANSI standard, indicating other RDBMS are using a similar approach for variable assignment. As a standard practice. Whereas, the SELECT statement is used within SQL Server.
3.A variable value will be NULL in case the query doesn’t return anything while using SET in variable assignment. Whereas, SELECT will ignore the query result keeping variable value unchanged.
4.The SET statement fails if multiple rows are return by the query. In the case of SELECT, it assigns arbitrarily/ last value to variable ignoring the consequences.
Let’s walk through various examples to understand the difference between these two options. This will help you to make a decision based on your scenario to use SELECT or SET.
Single vs Multiple Variable Assignment using SELECT / SET
Using SELECT, we can assign values to multiple variables in a single statement. In the following example, we can see that @name and @object_id variable assigned values in a single line of code.
Variable assignment using a SET statement expects us to mention each variable name individually. Using SET for multiple variable assignments is not allowed. Thus in the following example, we are using two SET options.
NULL Output – SELECT vs SET – Return Value
In the following example, the SELECT statement uses the default assigned values to variables even though the query has not returned any output.
Assigning @name = ‘Test’ and @object_id = 1000 as default values. Later trying to overwrite these values, however, the query doesn’t return any output thus variables are not overwritten. Although the SELECT statement retains the default values.
Here, in below example, default values are overwritten. Because query doesn’t return any results thus SET statement assigns NULL to @name and @object_id variables.
SET vs SELECT – Multiple Rows Output
Following example demonstrates the behaviour of a SELECT statement if multiple rows are returned by the query and used for variable assignment. If you run the following, subquery returning 3 rows as output.
Using SELECT to assign values to variables when a query returns multiple rows uses last return value. With SELECT, variables values are populated without giving away any warnings or error. Beware of this scenario as they can logically impact the code if these variables are used for future references.
Whereas, SET statement returns an error indicating subquery has return more than 1 row thus it’s unable to assign any random value to variables.
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
In summary, we have seen the difference between SELECT and SET statement. Using SELECT with multiple variable assignments, whereas SET doesn’t allow this. SELECT doesn’t return any NULL output in case query fails to return any output. But SET option assigns NULL to a variable. When Multiple rows are returned then SELECT randomly assigns the last value to a variable, whereas SET statement fails due to subquery returned more than 1-row error. If you like this post, you may read through tech-recipes database archives for more useful stuff.