BUG: Semi-Colon after GO followed by WITH

alicorn68alicorn68 Posts: 9 Bronze 1
in SQL Prompt
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.
Tagged:
· Share on Twitter

Best Answer

  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    edited February 6, 2019 2:35PM
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • VernRabeVernRabe Posts: 6 New member
    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]

    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    edited July 19, 2019 1:39PM
    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

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.