T-SQL: Prudent use of Select ... As

Every database developer using T-SQL will agree that the Select ... As feature allows one to easily grab values into a variable. However, after I got my fingers burnt recently, I had to share this to remind everyone how important it is to exercise prudence with its usage. Given the table below, assuming its an SQL table named Students.

Id	Name	Age	Class
1	Fade Ayomi	10	5
2	Oluwayomi Ojo	9	6
3	Kemi Mide	8	4

And given the SQL statements below which performs certain operations on the table given above

declare @StudentAge int;
select @StudentAge = Age from Students where Id = 1;
set @StudentAge = Age * 2;
select @StudentAge = Age from Students where Id = 5;
select @StudentAge as AgeSelected;

So what would AgeSelected resolve to according to the provided information? Ordinarily, one would expect AgeSelected to be NULL however, since @StudentAge carried a value initially, and the row described in the where clause do not exist in the table, @StudentAge will retain its previous value of 20 and therefore AgeSelected will be 20.

To prevent logical errors like this in code, always ensure to initialize the value of your variable. For instance you could modify the code above by adding the following code after line 3.

set @StudentAge = null;

Then go ahead to test for null after line 4 (which would now be line 5 when you have added the new line). Better still, just enclose the entire line 4 and 5 in if exists block. This ensures that line 5 only runs if the where condition is able to find a matching row. If you are a culprit of this oversight with code in production, ensure you immediately rewrite your codes, I know you'll not want embarrassment from this.