SQL Statements Where Semicolon is Required


By:   |   Updated: 2020-10-13   |   Comments   |   Related: More > T-SQL


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:

query results

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:

query 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:

query results

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:

query error

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:

query results

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:

query error

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:

query error

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


Last Updated: 2020-10-13


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips





Comments For This Article





download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

Split Delimited String into Columns in SQL Server with PARSENAME








get free sql tips
agree to terms