Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
3  
Not possible. The only way to enforce this is by nagging the people who write the code. –  Kermit Nov 5 '13 at 21:11
1  
Have a look at SqlCop: sqlcop.lessthandot.com/index.php It won't prevent people using bad code, but it can find it in you procedures and functions. –  Sebastian Meine Nov 6 '13 at 1:28
add comment

2 Answers 2

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.

share|improve this answer
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 Nov 6 '13 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 Nov 7 '13 at 8:56
add comment

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 than by reading what could be a very long and complex expression that spans lines or goes off the screen to the right.

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?

share|improve this answer
1  
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 Nov 6 '13 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 Nov 6 '13 at 14:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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