Sql170ScriptGenerator.GenerateScript removing required semicolon from BEGIN...END block within Stored Procedure creation
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 64/100
Research direction
Start with the provided C# reproduction, especially TSql170Parser and Sql170ScriptGenerator.GenerateScript, and trace how the BEGIN...END statement is rendered when IncludeSemicolons is enabled. Reproduce the output and verify that the generated procedure preserves the semicolon required before the following WITH CTE, then confirm the formatted SQL executes successfully.
Written by the indexing model from the issue text.
Description
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();
- Dominant language
- GAP
- Stars
- 277
- Forks
- 43
- Avg merge
- 6d 17h
- Merged PRs (30d)
- 3
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from microsoft/SqlScriptDOM
-
Difficulty 2/5 1-3 hours Newbie friendliness 84/100
microsoft/SqlScriptDOM#228 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
microsoft/SqlScriptDOM#183 ·
-
Difficulty 4/5 3-5 days Newbie friendliness 56/100
microsoft/SqlScriptDOM#226 · 1 reaction ·
-
Difficulty 4/5 3-5 days Newbie friendliness 48/100
microsoft/SqlScriptDOM#225 ·
-
Difficulty 3/5 1-2 days Newbie friendliness 58/100
microsoft/SqlScriptDOM#224 ·
All issues in microsoft/SqlScriptDOM
Similar issues
-
bug customer-eng Durable Agents Inngest status: needs triage
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 85/100
-
integration: elevenlabs
Difficulty 1/5 Under an hour Newbie friendliness 88/100
home-assistant/core#182944 · 1 comment ·
-
ai-observability bug team/ai-observability
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
vicharanashala/fln#563 ·