Breaking a String Across Multiple Lines

Question

I wrote a script which is connecting to an Oracle database to select multiple entries in a specific table.

The statement looks like this:

rs.open "SELECT PATH301 FROM NC301B WHERE EDIPROC like 'P30_' AND (LF301M > 0) AND (PATH301 NOT LIKE '%saptemp%') AND (PATH301 NOT LIKE '%SAPTEMP%') AND (PATH301 NOT LIKE '%usr%') AND (PATH301 NOT LIKE '%Windows%');", cn, 3 

Now I want to know if it is possible to split this statement over multiple lines. For example like this:

rs.open "SELECT PATH301 FROM NC301B  WHERE EDIPROC like 'P30_'  AND (LF301M > 0)  AND (PATH301 NOT LIKE '%saptemp%')  AND (PATH301 NOT LIKE '%SAPTEMP%')  AND (PATH301 NOT LIKE '%usr%')  AND (PATH301 NOT LIKE '%Windows%');", cn, 3 

That first SELECT Statement is way to big and not looking good at all. I hope you understand what I mean.

Answer 1

Well I'm going to post an answer anyway, never been a fan of the Fastest Gun in the West Problem

Using the Line Continuation Character

As I mentioned in the comments it sounds like you want to make the SQL command more readable in code. The normal way to do this is using the Line Continuation Character (_) also known as a Statement Break.

Dim sql sql = "SELECT PATH301 " & _   "FROM NC301B " & _   "WHERE EDIPROC like 'P30_' " & _   "AND (LF301M > 0) " & _   "AND (PATH301 NOT LIKE '%saptemp%') " & _   "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _   "AND (PATH301 NOT LIKE '%usr%') " & _   "AND (PATH301 NOT LIKE '%Windows%');" 

Bear in mind that this differs if we are not dealing with a string, for example to continue an If statement on to two lines would look something like this;

If result = ( _   condition1 _   And condition2 _   And condition3) Then 

Because we cannot break a string across lines we cheat by terminating the string continuing the line and then concatenating the string to the next string & _. This also means this will work;

Dim sql sql = "SELECT PATH301 " _   & "FROM NC301B " _   & "WHERE EDIPROC like 'P30_' " _   & "AND (LF301M > 0) " _   & "AND (PATH301 NOT LIKE '%saptemp%') " _   & "AND (PATH301 NOT LIKE '%SAPTEMP%') " _   & "AND (PATH301 NOT LIKE '%usr%') " _   & "AND (PATH301 NOT LIKE '%Windows%');" 

It's all down to personal preference and neither way has any performance benefit over its counterpart.

Personally, though I find this approach is more trouble than it's worth, especially when it comes to SQL strings.

Using String Concatenation

Say you want to test without one of the conditions let's say

AND (PATH301 NOT LIKE '%SAPTEMP%') 

trying to comment out that line will generate a

Microsoft VBScript compilation error:
Syntax error

Here is an example;

Dim sql sql = "SELECT PATH301 " & _   "FROM NC301B " & _   "WHERE EDIPROC like 'P30_' " & _   "AND (LF301M > 0) " & _   "AND (PATH301 NOT LIKE '%saptemp%') " & _   '"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _   "AND (PATH301 NOT LIKE '%usr%') " & _   "AND (PATH301 NOT LIKE '%Windows%');" 

Which makes it very inflexible, which is why I prefer to use String Concatenation to build up a string, like this;

Dim sql: sql = "" sql = sql & "SELECT PATH301 " sql = sql & "FROM NC301B " sql = sql & "WHERE EDIPROC like 'P30_' " sql = sql & "AND (LF301M > 0) " sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " sql = sql & "AND (PATH301 NOT LIKE '%usr%') " sql = sql & "AND (PATH301 NOT LIKE '%Windows%');" 

Although a little extra work it is far more flexible and allows you to test SQL strings by commenting out lines without affecting the entire SQL string, like so;

Dim sql: sql = "" sql = sql & "SELECT PATH301 " sql = sql & "FROM NC301B " sql = sql & "WHERE EDIPROC like 'P30_' " sql = sql & "AND (LF301M > 0) " sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " 'sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " sql = sql & "AND (PATH301 NOT LIKE '%usr%') " sql = sql & "AND (PATH301 NOT LIKE '%Windows%');" 

Another little thing I like to do is follow each line with & vbNewLine like this;

Dim sql: sql = "" sql = sql & "SELECT PATH301 " & vbNewLine sql = sql & "FROM NC301B " & vbNewLine sql = sql & "WHERE EDIPROC like 'P30_' " & vbNewLine sql = sql & "AND (LF301M > 0) " & vbNewLine sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " & vbNewLine sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " & vbNewLine sql = sql & "AND (PATH301 NOT LIKE '%usr%') " & vbNewLine sql = sql & "AND (PATH301 NOT LIKE '%Windows%');" 

That way when outputting the string (be in using Classic ASP, WScript etc) it formats correctly and when displaying in a HTML page (if using Classic ASP) you can easily use

sql = Replace(sql, vbNewLine, "<br />") 

to format it correctly, very useful when trying to debug problems with dynamic SQL.