Skip to content

Sql170ScriptGenerator.GenerateScript removing required semicolon from BEGIN...END block within Stored Procedure creation #219

Description

@mrowser6754

I attempted to format my T-SQL script using the GenerateScript method in the Sql170ScriptGenerator class. The following script was a test used to generate a stored procedure:

ORIGINAL:
CREATE PROCEDURE spDEBUG_ReleaseToolTestScript 
AS
IF EXISTS (SELECT 1) BEGIN;
	SELECT 1;
END;
WITH TEST AS (
	SELECT 1 AS One
)
SELECT *
FROM TEST;
GO
FORMATTED:
CREATE PROCEDURE spDEBUG_ReleaseToolTestScript
AS
IF EXISTS (SELECT 1)
    BEGIN
        SELECT 1;
    END
WITH   TEST 
AS     (SELECT 1 AS One)
SELECT *
FROM   TEST ;

Running the formatted SQL script will throw the following error in T-SQL:
Msg 319, Level 15, State 1, Line 88 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Code to reproduce:

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Diagnostics;

var sql = @"
CREATE PROCEDURE spDEBUG_ReleaseToolTestScript 
AS
IF EXISTS (SELECT 1) BEGIN;
	SELECT 1;
END;
WITH TEST AS (
	SELECT 1 AS One
)
SELECT *
FROM TEST;
GO";

Console.WriteLine("ORIGINAL:" + sql);

var reader = new StringReader(sql);
var parser = new TSql170Parser(true);

var fragment = (TSqlScript)parser.Parse(reader, out var errors);

if (errors.Count > 0)
{
    Console.WriteLine("Failed to parse SQL script:");
    foreach (var error in errors)
    {
        Console.WriteLine(error.Message);
    }

    Debugger.Break();
}

var options = new SqlScriptGeneratorOptions
{
    IncludeSemicolons = true,
    IndentationSize = 4
};
var generator = new Sql170ScriptGenerator(options);

generator.GenerateScript(fragment.Batches[0].Statements[0], out var formattedSql);

Console.WriteLine();
Console.WriteLine("FORMATTED:" + Environment.NewLine + formattedSql);
Console.ReadLine();

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions