SQL Prompt Fixes Always Have an Else

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.

2020-04-02 12_03_42-SQLQuery7.sql - Plato_SQL2017.sandbox (PLATO_Steve (57))_ - Microsoft SQL Server

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.

2020-04-02 12_04_22-SQLQuery7.sql - Plato_SQL2017.sandbox (PLATO_Steve (57))_ - Microsoft SQL Server

As with other fixes, you can click the light bulb and see the fixes.

2020-04-02 12_05_54-SQLQuery7.sql - Plato_SQL2017.sandbox (PLATO_Steve (57))_ - Microsoft SQL Server

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.

2020-04-02 12_06_03-SQLQuery7.sql - Plato_SQL2017.sandbox (PLATO_Steve (57))_ - Microsoft SQL Server

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.