Tag: Redgate

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.

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 BP002. This is where someone writes this code.

SELECT arts.ArticlesID
      , arts.Title
      , arts.Description
      , arts.PublishDate
      , arts.URL
  FROM dbo.Articles AS arts
  WHERE arts.PublishDate > DATEADD(WEEK,-2,arts.PublishDate)
  ORDER BY 1

I don’t see this a lot from Microsoft stack developers, but PHP, Java, and some other languages will do this.

This has been deprecated, but it’s a poor practice as well. The dependency between the ordering and column list is not good. This is an easy place to make mistakes over time as code is refactored.

If I do this, SQL Prompt gives me a green squiggly line below the constant.

If I put the cursor here, I get a lightbulb in the sidebar. Clicking that will give you some options.

If I click the first item, this will give me a placeholder where I can choose a column. We could replace this with the first column in the column list, but if you detect this during refactoring, the first item from the column list might not be the one you want.

I can start typing and intellisense takes over.

It’s a small thing, but this is a one way that increases code quality for developers that might not know better.

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.