SA0078 : Statement is not terminated with semicolon

Statements that are not terminated with a semicolon can cause problems in T-SQL scripts, impacting readability and future compatibility.

Description

Omitting the semicolon at the end of statements may lead to ambiguous or problematic script executions. The semicolon is the standard statement terminator in SQL, helping to clearly define where one statement ends and the next begins.

For example:

SQL
Copy
1-- Example of problematic query
2SELECT * FROM Employees
3SELECT * FROM Departments

In this example, the lack of semicolons makes it harder to quickly identify statement boundaries, which can lead to errors, especially as SQL Server continues to evolve.

  • It can lead to ambiguous syntax errors and reduce code readability, making maintenance and debugging more challenging.

  • Future versions of SQL Server might mandate semicolon usage for certain syntax elements, affecting script compatibility.

How to fix

This guide provides steps to add semicolons as statement terminators in T-SQL scripts to improve readability and ensure future compatibility.

Follow these steps to address the issue:

  1. Examine your T-SQL script to identify statements without a terminating semicolon. Statements like SELECT, INSERT, UPDATE, and DELETE should be checked.

  2. Add a semicolon at the end of each identified statement. This clearly defines where one statement ends and the next begins.

  3. Verify the script by running it in SQL Server Management Studio (SSMS) to ensure there are no syntax errors and that the script executes as expected.

For example:

SQL
Copy
1-- Corrected query with semicolons
2SELECT * FROM Employees;
3SELECT * FROM Departments;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Deprecated Features, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- Create procedure to retrieve error information.
 2CREATE PROCEDURE usp_GetErrorInfo
 3AS 
 4BEGIN   
 5    BEGIN TRY
 6    -- Generate divide-by-zero error.
 7
 8        SELECT  1 / 0  
 9    END TRY 
10            BEGIN CATCH 
11    -- Execute error retrieval routine. 
12
13        EXECUTE usp_GetErrorInfo -- gets error info
14    END CATCH;
15
16    BEGIN TRANSACTION;
17
18    IF @@TRANCOUNT = 0 
19    BEGIN 
20        ROLLBACK TRANSACTION 
21
22        PRINT N'Rolling back the transaction two times would cause an error.' 
23    END
24
25END
Example Test SQL with Automatic Fix
SQL
Copy
 1-- Create procedure to retrieve error information.
 2CREATE PROCEDURE usp_GetErrorInfo
 3AS 
 4BEGIN   
 5    BEGIN TRY
 6    -- Generate divide-by-zero error.
 7
 8        SELECT  1 / 0;  
 9    END TRY 
10            BEGIN CATCH 
11    -- Execute error retrieval routine. 
12
13        EXECUTE usp_GetErrorInfo; -- gets error info
14    END CATCH;
15
16    BEGIN TRANSACTION;
17
18    IF @@TRANCOUNT = 0 
19    BEGIN 
20        ROLLBACK TRANSACTION; 
21
22        PRINT N'Rolling back the transaction two times would cause an error.'; 
23    END;
24
25END;
Analysis Results
  Message Line Column
1 SA0078 : Statement is not terminated with semicolon. 25 0
2 SA0078 : Statement is not terminated with semicolon. 8 20
3 SA0078 : Statement is not terminated with semicolon. 13 16
4 SA0078 : Statement is not terminated with semicolon. 23 4
5 SA0078 : Statement is not terminated with semicolon. 20 17
6 SA0078 : Statement is not terminated with semicolon. 22 14
See Also

Other Resources

Analysis Rules