I guess this tip only applies to those of you that haven't fully jumped over to ORM yet and still write your own SQL. One of the habits that I've gotten into seems to have dramatically cut down the amount of SQL errors complaining about commas for me. It's very simple.

Instead of writing insert/update statements like such:

<cfquery name="insert">
INSERT INTO tablefoo(col1,col2)
VALUES(
<cfqueryparam value="#data1#">,
<cfqueryparam value="#data2#">
)
</cfquery>

I write them as:

<cfquery name="insert">
INSERT INTO tablefoo(col1,col2)
VALUES(
<cfqueryparam value="#data1#">
,<cfqueryparam value="#data2#">
)
</cfquery>

Why? It's easier for me to look down the list and scan for missing commas.

Anyway, just a stupid little tip that seems to be working for me. I doubt I'm the first that have done this. ;)

Derek Winstead wrote on 08/05/11 3:25 PM

I've found that makes it easy to append to the query as well. If later I need to add another column to the end I don't need to worry about forgetting the comma or missing the column before that didn't have an ending comma.

James Moberg wrote on 08/05/11 8:11 PM

I recently started doing this after reading it somewhere else. It is a LOT easier to visually troubleshoot.

Gary Fenton wrote on 08/05/11 8:14 PM

I wondered why various sql I've seen had commas at the start of the next line. And now the puzzle is solved. Right, back into the Mystery Machine, guys!

lex wrote on 08/06/11 7:07 AM

We had a contractor do some work for us and he spread this kind of syntax all over our sql. I think its extremely ugly and unnecessary. It only takes a second to fix the error IF you happen to forget the comma. I personally don't feel that its worth sacrificing the elegance of the code.

We have a few Oracle devs and I don't even see them do this.

James, F.E. wrote on 08/08/11 9:22 AM

It also makes it easier to comment out a line when debugging a query. in SQL Server you can just put -- in front of the line and it gets commented out without having to mess with other lines of the query.

Daniel Mendel wrote on 10/06/11 12:44 AM

I first saw this from some javascript developers who specify simple objects the same way -- I adopted it to use in SQL and have never looked back. Once you get used to it, it makes a world of difference.

Leave a comment