In today’s post, I go all the way back to the basics because of a logical bug I saw in production code recently.

It is always said to be a good practice to initialize variables within procedures so that you know what the default value is and what are valid and invalid values for a variable. For example, if the procedure does not have to deal with NULLs, then it is a good idea to initialize the variables as NULL so that a simple = NULL check is sufficient to tell whether we still have a default, invalid value or not.

The code below attempts to fetch the vacation hours for a given employee. The variables have default values defined, so it follows the variable initialization best practice mentioned above. In all probability, static code analysis/review would have never caught a logical bug hidden in the code below which raises it’s head when the code is run with an incorrect employee Id.

USE AdventureWorks2012;
GO

DECLARE @employeeVacationHours INT = -1;
DECLARE @employeeId INT;

--To be filled in by user
SELECT @employeeId = 777;

SELECT @employeeVacationHours = VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = @employeeId;

IF (@employeeVacationHours < 0)
PRINT 'Employee has invalid vacation hours.';

SELECT @employeeVacationHours AS VacationHours;

/* RESULT:

VacationHours
-------------
-1

*/

In this case, the SELECT statement which is supposed to fetch the vacation hours for the employee will not return any records. Because of this, the variable will continue to preserve it’s value and will not reset to the default value or NULL.

A SELECT statement that does not fetch anything also does not SET anything.

The scenario mentioned here is a simplified one – in the production code, this logical bug caused the procedure to go down a completely different code path causing various other “random” issues.

The solution to this problem? In this example, it’s quite simple – change the initialization value for the @employeeVacationHours variable to NULL and then check for NULL after the SELECT operation is complete.

Until we meet next time,

Be courteous. Drive responsibly.