If you’re running a subquery in SQL Server, but you’re getting error 102 that reads something like “Incorrect syntax near ‘;’“, there could be any number of reasons, because this is a generic error that simply means wrong syntax.

But one possible cause could be that you haven’t declared an alias for the subquery.

Example of Error

Here’s an example of code that produces the error:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS SumSalary
    FROM Employees
    GROUP BY DepartmentID
);

Output:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ';'.

This is an overly simplistic example, because I wanted to focus on the cause of the error.

In this case I haven’t declared an alias for the subquery, which is what caused the error.

Solution

As alluded to, we can fix this issue by declaring an alias for the subquery:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS SumSalary
    FROM Employees
    GROUP BY DepartmentID
) AS SalaryTotals;

That code runs without error.

All I did was add AS SalaryTotals to the end of the subquery. This is an alias that names the subquery. We could use this alias to explicitly refer to the subquery elsewhere in the query. For example, we could do this:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS SumSalary
    FROM Employees
    GROUP BY DepartmentID
) AS SalaryTotals
WHERE SalaryTotals.SumSalary > 100000;

Here, we’re explicitly stating that the SumSalary column we want is from the SalaryTotals subquery.