Is it acceptable to use the presence of a value in a NULLable column as a flag?

Question

I have a stored procedure that processes rows in a table. The stored procedure is called by an external process (Jitterbit, though I don't believe that is relevant). Occasionally the stored procedure throws an exception wchich is caught in a TRY CATCH block. My client wants this to work slightly differently; they want the error to be written back to the table (e.g. create a new column for the error information) which can then be periodically aggregated into a notification email. There will be another bit column (DEFAULT 0) that will be set to 1 when an error in that row is included in a notification email (so that the email is sent only once).

My question is this. If I make the column for the error message NULLable, and therefore do something like:

SELECT * FROM MyTable WHERE ErrorMessage IS NOT NULL AND EmailSent = 0;

is this an acceptable approach - to use the presence (or absence) of data in a column as a flag? My inner pedant thinks that another BIT column ought to be used - for example:

SELECT * FROM MyTable WHERE ErrorCreated = 1 AND EmailSent = 0;

Am I being pedantic?

score 3 · Accepted Answer · answered Jun 04 '20 at 13:02

NULL is not a value of the column but an internal metadata flag showing the column has no value. So duplicating this in user space by adding another flag column is a waste. NULL is a well-supported part of the relational model. Use it for its intended purpose.

Is it acceptable to use the presence of a value in a NULLable column as a flag?

1 Answers1