SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide you along and fix some bad code that your team might write out of habit.
SQL Prompt 10.1 released recently, and one of the recent fixes is for an issue that we’ve denoted as BP012. This is an issue where there is a CASE statement in code, but no ELSE has been defined. An example of this is shown below:
SELECT oli.OrderLineItemKey , oli.OrderID , oli.qty , CASE WHEN oli.qty < 10 THEN oli.unitcost WHEN oli.qty >= 10 AND oli.qty < 20 THEN oli.unitcost * .05 WHEN oli.qty >= 20 AND oli.qty < 100 THEN oli.unitcost * .1 END AS UnitCost , oli.linetotal FROM dbo.OrderLineItem AS oli
This is a problem because we often find some weird data being entered. You might think there will never be more than 100 ordered, but who knows. We should always have an ELSE clause, just in case.
Why? Here is why.
NULL is returned if you haven’t accounted for something.
If you hover over, you get the issue in a tip. If you put the cursor on the line, you get the lightbulb with the fixes on the side.
As with other fixes, you can click the light bulb and see the fixes.
When we click the top item, SQL Prompt will add an ELSE clause, with a placeholder and the cursor is here. You can start typing, and enter the expression or value that is appropriate.
This is another place where junior (or overloaded) developers may make a mistake. Having this in SQL Prompt allows this to be caught by the individual developer. Having this in SQL Code Guard (part of SQL Prompt and our automated build/deploy tools) and an automated build also means the code is evaluated automatically and this certainly doesn’t go to QA.
If you haven’t tried SQL Prompt, download an eval and see what you think. If you have it, upgrade and ensure you have all the code fixes.