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:
IF (1=1) BEGIN WITH numbers AS ( SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) SELECT * FROM numbersEND;
SQL Prompt will format it as
WITH numbers AS (
SELECT 1 AS num
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
)
SELECT * FROM numbers;
END;
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
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
IF (1=1) BEGIN
WITH numbers AS (
SELECT 1 AS num
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
)
SELECT * FROM numbers;
END;
This works, but is there a better way?
There were lots of discussions around this previously and it would be really helpful if you post your suggestion on user voice forum as it helps the product team to prioritise this task accordingly.
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?