SQL Server语法错误求助：T-SQL游标代码报Incorrect syntax near

Hey, let's break down the syntax error in your cursor code step by step—this is a super common pitfall in T-SQL, so we'll get it sorted quickly.

1. 核心语法错误：变量赋值缺少SET关键字 #

In T-SQL, you can't directly assign a value to a local variable with just 变量 = 值—you have to use the SET (or SELECT) keyword. The line triggering your syntax error is this one:

@varNewJoin_Dues_JF = @varJoiningFee;

That's invalid syntax. Fix it by adding SET at the start:

SET @varNewJoin_Dues_JF = @varJoiningFee;

2. 其他潜在语法/逻辑问题要排查 #

Even after fixing the assignment, there are a few more things to check to avoid follow-up errors:

• Variable declarations: Make sure @varNewJoin_Dues_JF and all other variables used in FETCH INTO are properly declared at the top of your script with matching data types to the columns returned by your cursor's SELECT query.
• IF statement code blocks: If you ever add more lines inside an IF condition, wrap them in BEGIN...END to avoid unexpected behavior. Even for single lines, it's a good habit for readability:

  IF @varcontractTypeName = 'New Join' AND @varNewPrepaidDues = 'Dues'
  BEGIN
      SET @varNewJoin_Dues_JF = @varJoiningFee;
  END
  

• Cursor loop termination: Don't forget to add a FETCH NEXT statement at the end of your WHILE loop—without it, the cursor will get stuck on the same row and run infinitely.
• Cursor cleanup: Always close and deallocate your cursor when you're done to free up server resources:

  CLOSE JF_PF;
  DEALLOCATE JF_PF;
  

修正后的简化代码示例 #

Here's a cleaned-up version of your core code with all the fixes applied:

-- 先声明所有局部变量（根据实际业务调整数据类型）
DECLARE @varRegion NVARCHAR(50),
        @varLocationId INT,
        @varTransDate DATE,
        @varcontractTypeName NVARCHAR(50),
        @varOriginalPrepaidDues NVARCHAR(50),
        @varNewPrepaidDues NVARCHAR(50),
        @varJoiningFee DECIMAL(18,2),
        @varPrepaidFeePackage NVARCHAR(100),
        @varNewJoin_Dues_JF DECIMAL(18,2);

-- 声明游标（补全你的完整SELECT查询）
DECLARE JF_PF CURSOR FOR 
SELECT a.Region,
       a.LocationId,
       a.TransDate,
       a.contractTypeName,
       a.OriginalPrepaidDues,
       a.NewPrepaidDues,
       a.JoiningFee,
       a.PrepaidFeePackage
-- FROM 你的表名 
-- WHERE 你的筛选条件;

OPEN JF_PF;
FETCH NEXT FROM JF_PF INTO @varRegion, @varLocationId, @varTransDate, @varcontractTypeName, @varOriginalPrepaidDues, @varNewPrepaidDues, @varJoiningFee, @varPrepaidFeePackage;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- 修复后的赋值逻辑
    IF @varcontractTypeName = 'New Join' AND @varNewPrepaidDues = 'Dues'
    BEGIN
        SET @varNewJoin_Dues_JF = @varJoiningFee;
    END

    -- 在这里添加其他业务逻辑

    -- 必须获取下一行数据，避免无限循环
    FETCH NEXT FROM JF_PF INTO @varRegion, @varLocationId, @varTransDate, @varcontractTypeName, @varOriginalPrepaidDues, @varNewPrepaidDues, @varJoiningFee, @varPrepaidFeePackage;
END

-- 清理游标资源
CLOSE JF_PF;
DEALLOCATE JF_PF;

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