How can we help you today? How can we help you today?

BUG: Semi-Colon after GO followed by WITH

Test code:
USE [master]
GO
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t]
After using menu "SQL Prompt->Insert Semicolons" that becomes:
USE [master];
GO; -- ISSUE IS HERE
WITH [test] AS (SELECT [a]=1) SELECT * FROM [test] [t];
Yes, the statement before a WITH needs to be terminated with a semi-colon. But not the GO.

Please fix this long standing bug.
alicorn68
0

Comments

4 comments

Sort by
  • 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
    Alex B
    0
  • 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
    0
  • 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]

    VernRabe
    0
  • 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

    Alex B
    0

Add comment

Please sign in to leave a comment.