Very Basic Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very Basic Question

Author

Topic

kirkm
Starting Member

1 Post

Posted - 2011-11-09 : 18:39:00

Hello...

Developing something and using (nicely) formatted SQL for the first time, thanks to the online instant SQL formatter.

Of the two examples below, if one preferable to the other?
Destined for older Excel VBA.

1.
sql = ""
sql = sql & "SELECT Format([Date Entered], ""mmm yyyy"") AS de, " & vbCrLf
sql = sql & " Space(4) & artist & String$(10, 9), " & vbCrLf
sql = sql & " title & String$(10, 9), " & vbCrLf
sql = sql & " [Label] & Space(1) & [Number] & String$(10, 9) AS lc, " & vbCrLf
sql = sql & " Cint(ch) & Iif(Val([CH]) = 1, "" week "", "" weeks""), " & vbCrLf
sql = sql & " high, " & vbCrLf
sql = sql & " Format([Date Left], ""mmm yyyy"") AS dl, " & vbCrLf
sql = sql & " tblepcharts.[40], " & vbCrLf
sql = sql & " tblepcharts.[40], " & vbCrLf
sql = sql & " tblepcharts.[40], " & vbCrLf
sql = sql & " tblepcharts.[40], " & vbCrLf
sql = sql & " tblepcharts.[40], " & vbCrLf
sql = sql & " linenumber, " & vbCrLf
sql = sql & " prefix " & vbCrLf
sql = sql & "FROM tblepcharts " & vbCrLf
sql = sql & "WHERE title LIKE ""%" & iPath & "%"" " & vbCrLf
sql = sql & "ORDER BY [Date Entered]"

2.
sql$ = "SELECT Format([DateEntered], 'mmm yyyy')AS DE, " & _
"Space(4) & tblepdata2.artist & String$(10, 9)," & _
"tblepdata2.title & String$(10, 9)," & _
"tblepdata2.composer & String$(10, 9)," & _
"tblepdata2.[Label] & Space(1) & [Cat] & String$(10, 9) AS LC," & _
"tblep.weekson & Iif(tblep.weekson = 1, ' week ', ' weeks')," & _
"tblep.peak," & _
"Format([DateLeft], 'mmm yyyy') AS dl, " & _
"tblep.blank, " & _
"tblep.blank, " & _
"tblep.blank, " & _
"tblep.blank, " & _
"linenumber, " & _
"[tblEPData2].[Prefix] & [tblEPData2].[SubLetter] AS prefix, " & _
"tblepdata2.cat AS [Number] " & _
"FROM tblep " & _
"INNER JOIN tblepdata2 " & _
"ON tblep.prefix = tblepdata2.prefix " & _
"WHERE tblepdata2.artist LIKE '%" & _
fred & _
"%' ORDER BY tblepdata2.orderbynum;"
sql = Replace(sql, "'", Chr(34))

Hmmm, the editor here has mucked it up a bit... not so clear now. But using the underscore, or not, and the sets of double quotes... or are both methods pretty much as good as each other ?

Thanks, Kirk

Just_Jeff
Starting Member

7 Posts

Posted - 2011-11-09 : 19:49:53

Its all just individual preference but for debugging purposes I prefer the first example. That way you can step through the code and watch the statement being built. If there is a problem you will know exactly where it is.

If nobody is ever going to see the SQL then just skip adding all the spaces.

-Jeff

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 19:50:48

Use which is easier (for you) to read in the VBA editor.

Like Jeff said, it's strictly a matter of preference. I like the 2nd better, he likes the 1st. Use what you prefer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources