SQL Prompt Fixes Poor Order By Coding

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.

2020-04-02 11_49_05-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

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

2020-04-02 11_49_13-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

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.

2020-04-02 11_51_20-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

I can start typing and intellisense takes over.

2020-04-02 11_49_29-CandidateList

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.

About way0utwest

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

2 Responses to SQL Prompt Fixes Poor Order By Coding

  1. Tom says:

    Assuming that “someone rights this code” should be “someone WRITES this code” ?

    Like

Comments are closed.