5

We found a lot of bugs are caused by missing comma,

select 
    aaaa,
    bbbb  -- forgot comma
    ccc,
    dddddddd,
.....

Is it a way to make expression name invalid and it must be expression as name in a SQL statement? (not globally because of missing "as" exists legacy code)

Improve this question
1

2 Answers 2

Reset to default
9

No, there is no way to force that syntax, unless you pass all queries through some 3rd party tool that parses the SQL code and supports looking for syntax like this, but I think it will be very difficult to even approach writing something that will do this.

It is also not possible to stop someone from typing FROM dbo.table nolock and not realizing that in that case nolock is an alias, not a locking hint. That is why this works:

SELECT name FROM sys.objects nolock WITH (NOLOCK);

In addition, I find the expression AS alias syntax far less usable than alias = expression - since, when I'm reviewing or troubleshooting code, I'm much more likely to need to find a column by its alias at the beginning of a line, than by reading what could be a very long and complex expression, that spans lines or goes off the screen to the right, and ends at a different vertical point than all the other lines. And since aliases should be shorter than expressions, it makes more sense to me to put the alias first.

At the end of the day, though, it's a preference, and no, there is no way to force it. Don't let your developers blame the language for their sloppiness. This is something that should be caught before a second set of eyes ever sees that query. I mean, how hard is it to count how many columns are returned by your query?

Improve this answer
2
  • 2
    For what it's worth, Aaron has convinced me to go with the alias = expression method. I started this a couple of months ago and haven't looked back. The code is much more readable, flowing, and other reasons Aaron has mentioned. I recommend it. And +1 to this answer.
    – Thomas Stringer
    Commented Nov 6, 2013 at 13:34
  • 1
    Thanks @Thomas! That post continues to attract very passionate responses, mostly calling me a heathen for violating the standards. shrug
    – Aaron Bertrand
    Commented Nov 6, 2013 at 14:31
8

As a possible workaround, if there was a rule with your company to always prefix column names with table aliases, a query like yours would never compile:

SELECT
  t.aaaa,
  t.bbbb   -- the missing comma results in "Incorrect syntax near '.'"
  t.ccc,
  t.dddddddd
  ...
FROM tablename AS t
  ...

This way whoever wrote or were reviewing the query would catch the issue very soon.

Of course, we are all human and we can forget adhering to rules easily too. Still, the method might result in fewer issues of this kind.

Improve this answer
2
  • 2
    +1 clever, but still relies on a human to consistently prefix all of their columns. You may as well also tell them to always put a comma at the end of every line. :-)
    – Aaron Bertrand
    Commented Nov 6, 2013 at 17:22
  • My reasoning is this. If you forget about the rule altogether, your query ends up with columns unprefixed and you thus risk running into the original issue, so here the hardest part is probably getting used to the rule. When you do remember about it, then a missing table alias might be easier to spot at a glance than a missing comma because the former would probably stand out among the rest to a greater degree than the latter. It is still possible to happen, of course, but then I would expect the chance of missing both the alias and the preceding comma to be less than that of missing either.
    – Andriy M
    Commented Nov 7, 2013 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.