[FORMATTING] GO Statement Before Procedure Declaration

[grantwforsythe]

Input data

SET
NOCOUNT ON;

GO

CREATE OR ALTER PROCEDURE dbo.sp_say_hi (@Name VARCHAR(25) = 'World')
AS BEGIN
    PRINT CONCAT('Hello, ', @Name);
END;

Expected Output

SET
NOCOUNT ON;

GO

CREATE
OR ALTER
PROCEDURE dbo.sp_say_hi (@Name VARCHAR(25) = 'World')
AS BEGIN
    PRINT CONCAT('Hello, ', @Name);
END;

The GO statement should be on it's own line.

Actual Output

SET
NOCOUNT ON;

GO CREATE
OR ALTER
PROCEDURE dbo.sp_say_hi (@Name VARCHAR(25) = 'World') AS BEGIN
    PRINT CONCAT('Hello, ', @Name);
END;

When executed on the database a fatal error occurs (Incorrect syntax was encountered while CREATE was being parsed) because of the GO.

Usage

• How are you calling / using the library?
  The library is being called from a Git hook with the below config:

  {
    "language": "tsql",
    "tab width": 4,
    "useTabs": false,
    "keywordCase": "upper",
    "indentStyle": "standard",
    "logicalOperatorNewline": "before",
    "expressionWidth": 80,
    "linesBetweenQueries": 2,
    "denseOperators": false,
    "newlineBeforeSemicolon": false
  }

• What SQL language(s) does this apply to?
  Transact-SQL/T-SQL
• Which SQL Formatter version are you using?
  Latest (v12.2.2)

[nene]

Thanks, the fix is now released in 12.2.2

[Jackesque]

Hi, somehow this issue still persists.

This is the desired output:

USE master;

GO
DROP DATABASE test;

GO
CREATE DATABASE test;

GO
USE test;

GO

Yet this is the output:

USE master;

GO
DROP DATABASE test;

GO CREATE DATABASE test;

GO USE test;

GO

Please address and fix it. I'm using the VSCode extension. My OS is Windows 10.

I tested in the website, and it gave the same result:

[nene]

Thanks for reporting.

The problem here is that this is not really about how the GO statement is supported, but rather how all other statements that can come after GO are supported. I threw in some support for CREATE/ALTER/DROP DATABASE and USE statements. But that's only a needle in the haystack - a lot more is missing. I personally have zero experience with the Microsoft SQL stack. Somebody contributed the Transact-SQL support a long time ago, but turns out that he simply copy-basted the chapter titles from the documentation as syntax, which doesn't really work :P So as a result the Transact-SQL support in SQL Formatter is really pretty crappy. Additionally I've come to understand that there really are multiple SQL languages gathered under the Transact-SQL label. The primary one is for SQL Server, but then there's also Azure and various other things. So this probably should be split up to multiple dialects on SQL Formatter side. Buty I'm not too keen on working on improving this as I find the syntax diagrams in MS docs to be kinda hard to comprehend.

I should point out though that you haven't selected Transact-SQL as the SQL dialect you're formatting (see also the FAQ):

Given the state of Transact-SQL support, this might actually make some things worse.

[Jackesque]

After changing the dialect, the result did change, just not in any significantly helpful way. Thanks for the reply though.

The result using Transact-SQL:

USE master;

GO DROP
DATABASE test;

GO CREATE
DATABASE test;

GO USE test;

GO

Desired output:

USE master;

GO
DROP DATABASE test;

GO
CREATE DATABASE test;

GO
USE test;

GO

[nene]

The fix for the latter has now been released in 15.5.2