By: Sergey Gigoyan | Updated: 2020-10-13 | Comments (2) | Related: > TSQL
Problem
The semicolon (;) is used in SQL code as a statement terminator. For most SQL Server T-SQL statements it is not mandatory. Having said that, according to Microsoft documentation a semicolon will be required in future versions of SQL Server. I personally use semicolons only when it is mandatory, because that is how I am used to coding and, in my view, these "future version statements about features going away" are quite uncertain. I believe that many developers do not include a ";" as a statement terminator and this would cause a lot of issues if this was suddenly mandatory for all T-SQL code. There are some T-SQL statements that do require a ";" or you will get errors and we will cover these in this article.
Solution
We are going to discuss three T-SQL statements that require a semicolon. We will reproduce errors that are caused due to the missing semicolon and provide the correct syntax for these statements. Well, let’s start:
Common table expression (CTE) using semicolon terminator
The first statement that we will discuss is the T-SQL for creating a common table expression (CTE).
The T-SQL below simply selects numbers from 1 to 100 using CTE:
WITH Numbers AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM Numbers WHERE n<100 ) SELECT n AS Number FROM Numbers OPTION (MAXRECURSION 0)
As we can see, the statement runs without errors and 0-100 numbers are selected:
Well, now let’s modify it a bit and add one more SELECT statement:
SELECT 1 AS '1' WITH Numbers AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM Numbers WHERE n<100 ) SELECT n AS Number FROM Numbers OPTION (MAXRECURSION 0)
Oops, we received an error:
The error message is quite clear and it is not difficult to understand the reason – the previous statement of a common table expression statement must be terminated with a ";". The reason for this is that if a CTE is used inside batch a ";" is required after the previous statement.
In the previous T-SQL code we used a CTE without a ";" and there were no errors, because there were no statements before it.
To fix the issue, we just add a ";" after the first SELECT statement:
SELECT 1 AS '1' ; WITH Numbers AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM Numbers WHERE n<100 ) SELECT n AS Number FROM Numbers OPTION (MAXRECURSION 0)
The code runs without errors:
If we add "GO" after the first select statement and remove ";" after it, the T-SQL code will again run successfully as we have two separate batches. Therefore, the CTE will be the first statement in the second batch so a ";" will not be mandatory:
SELECT 1 AS '1' GO WITH Numbers AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM Numbers WHERE n<100 ) SELECT n AS Number FROM Numbers OPTION (MAXRECURSION 0)
MERGE statement using semicolon terminator
The next statement requiring a semicolon is the MERGE statement. MERGE allows us to run INSERT, UPDATE, and DELETE statements in a single command and can be very useful while synchronizing tables. This statement is another one of those rare T-SQL statements requiring a semicolon.
Let’s illustrate it by an example. We create two sample temporary tables with sample data:
CREATE TABLE ##tmpTableA
(
ID INT PRIMARY KEY,
Val INT
)
INSERT INTO ##tmpTableA(ID, Val)
VALUES(1,10), (2,20), (4,40)
CREATE TABLE ##tmpTableB
(
ID INT PRIMARY KEY,
Val INT
)
INSERT INTO ##tmpTableB(ID, Val)
VALUES(1,100), (3,30)
Now we are trying to synchronize these tables using the MERGE statement:
MERGE ##tmpTableA AS t
USING ##tmpTableB AS s
ON s.ID=t.ID
WHEN MATCHED AND t.Val<>s.Val
THEN UPDATE
SET t.Val=s.Val
WHEN NOT MATCHED
THEN INSERT (ID, Val)
VALUES(s.ID, s.Val)
SELECT * FROM ##tmpTableA
We receive an error message that clearly states that a ";" is mandatory in this statement:
Therefore, we just add a ";" after the MERGE statement and everything works fine:
MERGE ##tmpTableA AS t
USING ##tmpTableB AS s
ON s.ID=t.ID
WHEN MATCHED AND t.Val<>s.Val
THEN UPDATE
SET t.Val=s.Val
WHEN NOT MATCHED
THEN INSERT (ID, Val)
VALUES(s.ID, s.Val)
;
SELECT * FROM ##tmpTableA
As we can see, the code successfully completed and data in the table is updated:
Hence, it is important to remember that semicolon is mandatory after the MERGE statement.
THROW using semicolon terminator
The third statement that can require a ";" is the THROW statement. Let’s see when the semicolon is mandatory with THROW.
This statement raises an exception and transfers execution to the CATCH block if TRY...CATCH is used. Otherwise, the statement batch is terminated.
In the code below, we use a THROW statement without parameters inside the CATCH block to raise the caught exceptions:
BEGIN TRY
INSERT INTO ##tmpTableA(ID, Val)
VALUES (1, 1000)
END TRY
BEGIN CATCH
PRINT 'Error'
THROW
END CATCH
When we execute the code, we receive an error message:
This is because the statement before the THROW must be terminated by a semicolon. So, we fix the problem by adding a ";’" after the first command in the CATCH block:
BEGIN TRY
INSERT INTO ##tmpTableA(ID, Val)
VALUES (1, 1000)
END TRY
BEGIN CATCH
PRINT 'Error';
THROW
END CATCH
After doing that, we do not receive the syntax error above and we get the error message as expected:
This means that the code works fine and the error messages caught in a CATCH block are raised. If we move the first statement in the CATCH block, the code works as expected and there is no syntax error:
BEGIN TRY
INSERT INTO ##tmpTableA(ID, Val)
VALUES (1, 1000)
END TRY
BEGIN CATCH
THROW
END CATCH
This is because there are no other statements before the THROW in the CATCH block, so a ";" is not mandatory.
Conclusion
To sum up, while most T-SQL statements do not require a semicolon as a statement terminator, there are still some commands where the usage of a ";" is mandatory. Being familiar with the syntax of these commands is important in order to use a ";" correctly and avoid errors.
Next Steps
- Check out these other T-SQL tips
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-10-13
