this is a nice article, showing the danger of this kind of SQL code.
Actually there are many people not knowing the detailled behaviour of this code, especially the part where the select returns no rows. It's kind of logical, we just are not aware of it when using it.
I personally try to avoid this code at all, mainly for 2 reasons:
1) unexpected behaviour, as this article is about
2) performance: if the select is running against a huge table it really makes difference
If I have to assign only 1 variable I use a subselect, as it really assigns null to the variable when no row is returned, or an exception, if more than one is returned (an exception I want to have to get to know of the situation).
If I have to deal with assigning multiple variables, my first thought is:
Do I really need variables, or can I do it using temp tables or table variables?
Many of us tend to not think of tables, when "needing just 2 or 3 variables". But try it, you will discover new ways of solving queries.