[SA 7.5.502] - Copy SQL Code as:

[SA 7.5.502] - Copy SQL Code as:

I'm not quite sure if this is proper when converting the statement to VBScript:

Code:

Dim sql
sql = "SELECT " & vbCrLf _
& " RunNo, " & vbCrLf _
& " RunID, " & vbCrLf _
& " InstrumentID, " & vbCrLf _
& " RunStartDate, " & vbCrLf _
& " Analyst, " & vbCrLf _
& " Comments, " & vbCrLf _
& " Made, " & vbCrLf _
& " Updated, " & vbCrLf _
& " UpdateBy, " & vbCrLf _
& " IcalDate, " & vbCrLf _
& " ColumnID, " & vbCrLf _
& " CalID, " & vbCrLf _
& " [Active], " & vbCrLf _
& " CurveEq, " & vbCrLf _
& " MadeBy, " & vbCrLf _
& " AutoRef, " & vbCrLf _
& " AnalRunStamp, " & vbCrLf _
& " InUse, " & vbCrLf _
& " InUseBy " & vbCrLf _
& "FROM " & vbCrLf _
& " [DHL].[dbo].[AnalRun]"

Generally code continuations in VBScript/VBA are simply "& _ " and not "& vbCrLf _"

All this seems to do is 'prettify' the SQL statement when if you actually print the statement out.

Where removing the leading "&" and the trailing vbCrLF gets you something more like:

	Code:
	Dim sql sql = "SELECT " & _ " RunNo, " & _ " RunID, " & _ " InstrumentID, " & _ " RunStartDate, " & _ " Analyst, " & _ " Comments, " & _ " Made, " & _ " Updated, " & _ " UpdateBy, " & _ " IcalDate, " & _ " ColumnID, " & _ " CalID, " & _ " [Active], " & _ " CurveEq, " & _ " MadeBy, " & _ " AutoRef, " & _ " AnalRunStamp, " & _ " InUse, " & _ " InUseBy " & _ "FROM " & _ "AnalRun"

It there really a point to 'prettifying' the SQL statement for execution?

If no, is there a way to adjust the syntax of the "Copy SQL as.."?

Hi. Yes you can customize it. In the same menu where you click Copy SQL Code as -> VBScript, click the last menu item Modify Language Formats...
Hope this helps.

Ok getting closer.

Now I just have a trailing and leading space in columns:

	Code:
	Dim sql sql = "SELECT " & _ " RunNo, " & _ " RunID, " & _ " InstrumentID, " & _ " RunStartDate, " & _ " Analyst, " & _ " Comments, " & _ " Made, " & _ " Updated, " & _ " UpdateBy, " & _ " IcalDate, " & _ " ColumnID, " & _ " CalID, " & _ " [Active], " & _ " CurveEq, " & _ " MadeBy, " & _ " AutoRef, " & _ " AnalRunStamp, " & _ " InUse, " & _ " InUseBy " & _ "FROM " & _ " [DHL].[dbo].[AnalRun]"

Any way to remove that leading space from column names? It doesn't apply to the "FROM" criteria.

They will be gone if you remove leading and trailing spaces in the Line Separator option

	SysOp wrote:
	They will be gone if you remove leading and trailing spaces in the Line Separator option

There is no trailing space in the line separator:

*Edit: removing the leading space from the top quote (" & _) removes the space after the comma in the columns..

Remove spaces around quotes, and it should do what you want.

	SysOp wrote:
	Remove spaces around quotes, and it should do what you want.

I've done that.

So, it turns out:

IF I'm converting a statement I wrote with no whitespace before the columns such as this:

It converts fine.

But if I right click a table and do "copy code as" I get the leading spaces on the columns no matter how I set my line separator preferences.

HOWEVER:

If I format my code with Ctrl-F11 and then convert it, it gets even wonkier.

Perhaps the conversion should not take formatted whitespace into account?

Here's a video of me converting a table DDL to VBScript:

http://screencast.com/t/JRyLAZl0

Note the leading spaces. Again no matter how I adjust my line separator, I can get rid of the trailing spaces after column commas but not leading spaces before the column name inside the quotation marks.

It literally takes every line in the highlighted text, appends to each line the text specified in Line Separator, concatenates all that back and finally adds prefixes and suffixes from Statement Prefix and Statement Suffix options. The mechanics of that function are pretty simple.

These leading spaces are in your code in the editor. If you remove them, they will be gone from the results. There isnt any automatic trimming or any kind of code parsing.

Last edited by SysOp on Thu Oct 06, 2016 3:13 pm; edited 1 time in total

	SysOp wrote:
	These leading spaces are in your code in the editor. If you remove them, the will be gone from the results. There no automatic trimming or any kind of code parsing.

Right, but there's still at least one leading space being added in certain instances (conversion of full tables for example).

I'm sorry I'm not following.

	Quote:
	Conversion of full tables

what's that?

SysOp wrote:

I'm sorry I'm not following.

	Quote:
	Conversion of full tables

what's that?

Watch my screencast above where I convert the table DDL to a select statement (vbscript)

here's the URL again: http://screencast.com/t/JRyLAZl0

Sorry here's a smaller video:

http://screencast.com/t/WDgqUvLhld