WITH keyword missing semicolon

[cptb]

Hi Bruce,
Recently parsing some of the queries I have seen involving CTE WITH keyword. For queries start with the WITH keyword (first token in the entire script), it is fine. However, if the WITH is not the very first token, the parser seems to remove the leading semicolon which invalids the entire script.

For example, the following demo script shows the problem:

CREATE TABLE #t (
    footpath_id int, 
    road_id int, 
    footpath_position varchar(5));

INSERT INTO #t 
SELECT top 100 footpath_id, road_id, footpath_position
FROM footpath;

;WITH my_cte (fid, rid, pos)
AS
(
    SELECT footpath_id fid, road_id rid, footpath_position pos FROM #t
)
select fid, rid, pos, r.road_name from my_cte 
join roadnames r on r.road_id = my_cte.rid

The above script has semicolon for each statement. Also an extra semicolon was added in front of the WITH. But after parsing, it seems all semicolon were removed which makes the WITH invalid.

Is there a way to retain the semicolon while parsing the script?
Thank you very much.

Paul

[bruce-dunwiddie]

It's currently intentional that it gets dropped from statements. it'd be no different than a comment there, as it does not really serve a purpose to the statement, and if anything would belong to the preceeding statement, or be a standalone No-Op statement.

I understand that the language guidance has all CTE's be prefixed with it, but that's really just a workflow hack. You can run the statement without a semicolon, as long as there's no preceeding statement or the preceeding statement is ended with a semicolon, which is a long standing breaking change deprecation path requirement anyways.

It'll be a really strange solution in the code to make it work another way, especially since CTE's already have an odd flow that shows that they kind of go against the language base structure of having the first word identify the command.

I'm always up for discussing however.

What issue does it cause you? Are you needing to replay 100% characters from the original script?

[cptb]

Hi Bruce,
Thanks for your explanation. I mainly use your parser to put a script into different sections (statement) first, and then use our own internal parser to analyse each section in details to make sure special business rules are applied. Once our process completes, we will put all statements together again then send for execution. So we do rely on all valid SQL token to be retained.
However, at our end, we can easily add a semicolon to either the beginning or the end of each statement. Or we can also only add a leading semicolon when we see WITH. It does feel weird especially the WITH syntax, I have never liked it although CTE does provide neat capabilities.

Paul

[bruce-dunwiddie]

My suggestion would be to always both prepend and postfix a semicolon on to all statements, unless this causes some other issue in your environment. It should not cause any issues from SQL Server's perspective. That way you don't have to add conditional checks.

[cptb]

Yeah, Sql server Does not care how many extra semicolons there. I will try your suggestion next Monday and will feedback you after.
Thank you very much.