3

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?

Edwardo
  • 133
  • 3
  • Welcome to DBA.SE. Your question might be closed as opinion-based: This question is likely to be answered with opinions rather than facts and citations. It should be updated so it will lead to fact-based answers. However, ask the customer what they think would be the better solution. They are "paying", so they normally get to decide which solution they would prefer and which solution will be implemented. Good luck. I'd personally go with the first approach, because it is slick and doesn't require an additional column, but that is MOO (My Opinion Only). – John K. N. Jun 04 '20 at 08:55
  • I'm asking for what is considered best practice? Is that not a valid question? I'm not going to ask my clients because they manufacture widgets and know nothing about database design. – Edwardo Jun 04 '20 at 09:14
  • Best practice for most things in SQL Server is very much opinionated. There can't be a correct technical answer for anyone but the people who share (or can be convinced of) that specific opinion. It's a tough line to draw, and I don't have any hard opinions one way or the other in this specific case (and plenty of counter-examples exist here), but "which way should I do something" is often a question better suited for a forum or chat than a Q & A resource. – Aaron Bertrand Jun 04 '20 at 13:23
  • 1
    As a (related!) example, Why shouldn't we allow NULLs? is quite subjective, and probably wouldn't have survived as a new question today. The guidelines we use on the site (which are also, by definition, subjective) have evolved over time... some background here but this might be slightly outdated too. – Aaron Bertrand Jun 04 '20 at 13:28
  • Ok thanks. If it gets closed it gets closed. Sorry if I've transgressed! – Edwardo Jun 04 '20 at 13:48
  • No problem, I agree it's mostly an opinion, as both approaches are valid. It might depend a bit on how many rows you have and how many of those contain errors as well as the data type of the errormessage column and how you index it. I think there is a valid argument for both approaches. Personally I would not add an extra column as long as the ErrorMessage isn't a text column (which it shouldn't be), but that's my opinion. Other people have strong opinions on everything involving NULL – Tom V Jun 04 '20 at 16:53

1 Answers1

3

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.

Michael Green
  • 24,839
  • 13
  • 51
  • 96