如何解决SQL错误：关键字'SELECT'附近存在语法错误？

Hey Rozbeh, let's work through fixing that SQL syntax error you're running into, plus address a few other hidden issues in your query:

Let's break down the problems step by step and put together a corrected version:

1. Missing Variable Declaration #

You're using the @Num variable to calculate the difference between @Number and @Number2, but you never declared it. Add this line with your other declarations:

DECLARE @Num INT

2. Invalid Variable Assignment Syntax #

When assigning a value from a subquery to a scalar variable, you must wrap the entire SELECT statement in parentheses. Your original line SET @Date = SELECT TOP (@Num)... violates this rule—this is exactly what's causing the "syntax error near SELECT" message.

3. Data Type Mismatch & Logical Issue #

• YEAR(Tarikh) returns an integer (e.g., 2024), but @Date is declared as DATETIME2—these types don't align. If you only need the year value, change @Date to an INT; if you need a full date, adjust the query to return the actual Tarikh column.
• TOP (@Num) will return multiple rows if @Num is greater than 1, which can't be assigned to a single variable. You'll need to ensure you're fetching only one row (like using TOP 1 with an ORDER BY to make the result predictable, or ensuring @Num always equals 1).

Corrected Query (If You Want the Year as an Integer) #

DECLARE @Year INT -- Changed type since we're fetching a year number
DECLARE @Number INT 
DECLARE @Number2 INT 
DECLARE @Num INT -- Added missing declaration

SET @Number = 90 
SET @Number2 = 89 
SET @Num = @Number - @Number2 
SET @Year = (SELECT TOP (@Num) YEAR(Tarikh) FROM Final) -- Wrapped subquery in parentheses

Corrected Query (If You Need a Full DATETIME2 Value) #

If you intended to get a complete date instead of just the year, adjust the query to return the Tarikh column directly, and ensure you only fetch one row:

DECLARE @Date DATETIME2 
DECLARE @Number INT 
DECLARE @Number2 INT 
DECLARE @Num INT 

SET @Number = 90 
SET @Number2 = 89 
SET @Num = @Number - @Number2 
-- Added ORDER BY to make the TOP result predictable
SET @Date = (SELECT TOP 1 Tarikh FROM Final ORDER BY Tarikh DESC)

These changes will resolve the syntax error, plus fix the variable declaration and type mismatch issues that would have caused problems next.

内容的提问来源于stack exchange，提问作者Rozbeh