SA0078 : Statement is not terminated with semicolon

Introduction

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:

-- Example of problematic query
SELECT * FROM Employees
SELECT * 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:

-- Corrected query with semicolons
SELECT * FROM Employees;
SELECT * 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
BEGIN
    BEGIN TRY
    -- Generate divide-by-zero error.

        SELECT  1 / 0
    END TRY
            BEGIN CATCH
    -- Execute error retrieval routine.

        EXECUTE usp_GetErrorInfo -- gets error info
    END CATCH;

    BEGIN TRANSACTION;

    IF @@TRANCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION

        PRINT N'Rolling back the transaction two times would cause an error.'
    END

END

Example Test SQL with Automatic Fix

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
BEGIN
    BEGIN TRY
    -- Generate divide-by-zero error.

        SELECT  1 / 0;
    END TRY
            BEGIN CATCH
    -- Execute error retrieval routine.

        EXECUTE usp_GetErrorInfo; -- gets error info
    END CATCH;

    BEGIN TRANSACTION;

    IF @@TRANCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;

        PRINT N'Rolling back the transaction two times would cause an error.';
    END;

END;

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

Analysis Rules