Insert Semicolon Issue with CTEs
SloopJohnB
Posts: 25 Bronze 3
in SQL Prompt
SQL Prompt's "Insert semicolon" function behaves weirdly when a CTE is a the start of a control loop. For example, if I have this code:
SQL Prompt will format it as
While the code runs, I think the semicolon after "BEGIN" makes very little sense. First, the beginning of a control loop generally isn't terminated with a semicolon – just the end. Second, the code without the semicolon after "Begin" runs just fine, since the "IF..." statement doesn't count towards the "last line must end in a semicolon" rule.
This actually matters to me because I tend to use straight "Begin/End" pairs to denote code regions. So I will have code such as:
So the semicolon after "begin" throws everything off when I collapse everything and just draws attention to its goofiness. Also, I sometimes just get outright errors when I try to format the code and then have to insert the unnecessary semis myself. So I end up doing stuff like
This works, but is there a better way?
<div>IF (1=1) BEGIN</div><div> WITH numbers AS (</div><div> SELECT 1 AS num</div><div> UNION SELECT 2</div><div> UNION SELECT 3</div><div> UNION SELECT 4</div><div> )</div><div> SELECT * FROM numbers</div><div>END;</div>
SQL Prompt will format it as
<div>IF (1=1) BEGIN;</div><div> WITH numbers AS (</div><div> SELECT 1 AS num</div><div> UNION SELECT 2</div><div> UNION SELECT 3</div><div> UNION SELECT 4</div><div> )</div><div> SELECT * FROM numbers;</div><div>END;</div>
While the code runs, I think the semicolon after "BEGIN" makes very little sense. First, the beginning of a control loop generally isn't terminated with a semicolon – just the end. Second, the code without the semicolon after "Begin" runs just fine, since the "IF..." statement doesn't count towards the "last line must end in a semicolon" rule.
This actually matters to me because I tend to use straight "Begin/End" pairs to denote code regions. So I will have code such as:
BEGIN -- Do Something<br><some sql><br>END;<br><br>BEGIN -- Do something else<br><more sql><br>END;
So the semicolon after "begin" throws everything off when I collapse everything and just draws attention to its goofiness. Also, I sometimes just get outright errors when I try to format the code and then have to insert the unnecessary semis myself. So I end up doing stuff like
<div>IF (1=1) BEGIN<br> ;<br></div><div> WITH numbers AS (</div><div> SELECT 1 AS num</div><div> UNION SELECT 2</div><div> UNION SELECT 3</div><div> UNION SELECT 4</div><div> )</div><div> SELECT * FROM numbers;</div><div>END;</div>
This works, but is there a better way?
Tagged:
Answers
Tianjiao Li | Redgate Software
Have you visited our Help Center?