Use vbCrLf in Embedded SQL Scripts

Use vbCrLf in Embedded SQL Scripts

Visual Basic contains quite a few intrinsic constants -- that is, constants that are now part of the VBA language but that you used to have to define in your code either explicitly or by adding the Constant.Bas file to your project. One of these is vbCrLf, which equates to the carriage return/line feed -- or Chr$(13) & Chr$(10) - character combination.

But why would I suggest that you include this constant in an embedded SQL script? After all, the code means nothing to SQL Server; it could care less that you want a carriage return and line feed at the end of each line. The answer: debugging, plain and simple. Quite often, you'll find yourself creating embedded SQL scripts that run to 10, 20 or more lines. For example,

sSQL = "SELECT * FROM anytable" _
& " WHERE userid = " & lUser _
& " AND color = '" & sColor "'"

If you define the SQL statement in this way, the SQL script is readable when viewed in the procedure, but what about when you want use the Immediate window to see the value of the sSQL variable at run time?

You enter
? sSQL

in the Immediate window, and a long, unbroken string is shown, running miles off into the distance.

However, if you simply suffix each line with vbCrLf, like this:

sSQL = "SELECT * FROM anytable" & vbCrLf _
& "WHERE userid = " & lUser & vbCrLf _
& "AND color = '" & sColor "'"

then you can quickly and easily read your completed SQL code to find the problem.

The previous example illustrates another benefit of vbCrLf: if you don't use it, you must remember to start (or end) each line with a space; otherwise,

sSQL = "SELECT * FROM anytable" & _
& "WHERE userid = " & lUser

becomes
"SELECT * FROM anytableWHERE userid = 1"

and your code will generate a SQL syntax error.