BUG: Semi-Colon after GO followed by WITH

alicorn68

Test code:

USE [master];
GO; -- ISSUE IS HERE
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t];</code>USE [master]
GO
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]</pre></div><div>After using menu "SQL Prompt->Insert Semicolons" that becomes:<br><div><pre class="CodeBlock"><code>

Yes, the statement before a WITH needs to be terminated with a semi-colon. But not the GO.

Please fix this long standing bug.

Alex B

Hi @alicorn68,

This has been fixed in 9.4.12 in that it no longer adds a semicolon after the GO to make the statement invalid.  However it now does not insert the semicolon before the WITH statement at all.

So this:

<div>USE [master]
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">GO
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]</span></div>

becomes:

<div>USE [master];
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">GO
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t];</span></div>

When inserting the semicolon only.

Kind regards,
Alex

Alex B

Hi Brad,

Ah, righto I see what you mean - if you only add the semicolons it puts the semicolon after the GO.

If you format the query (ctrl+k, ctrl+y) it will actually place things correctly:

<div>USE [master]
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">GO
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]</span></div>

becomes

<div>USE [master];
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">GO
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">WITH [test]
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">AS (SELECT 1 AS [a])
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">SELECT *
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: roboto, "helvetica neue", Arial, sans-serif;">FROM&nbsp; &nbsp;[test] AS [t];</span></div>

I've raised this with the team so that they are aware.

Kind regards,
Alex

VernRabe

I'm not sure of the status of this, but in 9.5.8.10521 the semicolon after the GO is still inserted if the GO is followed by a comment, e.g., a semicolon will not be added after the GO for the following script.

USE [master]
GO
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]

but it will for the following script

USE [master]
GO --
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]

Alex B

Hi @VernRabe,

I see what you mean, your second entry becomes:

<div>USE [master];
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">GO
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">; --
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">WITH [test]
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">	AS ( SELECT [a] = 1 )
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">SELECT *
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">FROM [test] [t];</span></div>

Which has the semicolon before the WITH as is tradition, but it still does not, as I mentioned in the reply above marked as the answer when there is no comment.

In either case, it no longer breaks the script, but it's good to know this behavior!

Kind regards,
Alex