使用CURSOR游标遇语法错误，请求SQL代码排查协助

嘿，看起来你在游标使用上遇到了几个语法小问题，我帮你逐一排查并修复：

问题分析 #

1. 字符串常量误用双引号：SQL Server中字符串常量需要用单引号包裹，你代码里的"MIG"会被数据库识别为标识符（比如列名），这会导致额外的语法错误。
2. 游标声明的非标准写法：虽然SQL Server允许先声明游标变量再赋值，但这种写法在某些场景下可能触发解析错误，更稳妥的是直接在DECLARE语句中定义游标。
3. 老式表连接写法：你用逗号分隔两个表的写法不够清晰，建议改用显式的INNER JOIN，提升代码可读性同时避免潜在的逻辑问题。

修复后的完整代码 #

DECLARE @maxObject INT
DECLARE @maxVcoObject INT
DECLARE @pointerVcontract INT
DECLARE @aantalObjecten INT
DECLARE @cursor CURSOR FOR 
    SELECT vc.p_vcontract 
    FROM vcontract vc
    INNER JOIN vco_partcp vp ON vc.p_vcontract = vp.p_vcontract 
    WHERE vc.p_vcontract NOT IN (SELECT p_vcontract FROM vco_object)

SET @maxVcoObject = (SELECT MAX(p_vco_object) FROM vco_object)
SET @maxObject = (SELECT MAX(p_object) FROM object)
SET @aantalObjecten = (SELECT COUNT(*) FROM vcontract WHERE p_vcontract NOT IN(SELECT p_vcontract FROM vco_object))
SET @maxObject = @maxObject - @aantalObjecten

OPEN @cursor
FETCH NEXT FROM @cursor INTO @pointerVContract
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @maxVcoObject = @maxVcoObject + 1
    SET @maxObject = @maxObject + 1
    INSERT INTO vco_object (p_vco_object, p_vcontract, p_object, create_date, create_user, last_date, last_user) 
    VALUES (@maxVcoObject, @pointerVContract, @maxObject, GETDATE(), 'MIG', GETDATE(), 'MIG')
    FETCH NEXT FROM @cursor INTO @pointerVContract
END
CLOSE @cursor
DEALLOCATE @cursor

额外优化建议 #

如果你的数据量较大，游标操作的效率通常会比集合型操作低很多。你可以考虑用ROW_NUMBER()窗口函数生成连续的ID值，然后一次性插入数据，比如：

WITH MissingVcontracts AS (
    SELECT vc.p_vcontract,
           ROW_NUMBER() OVER (ORDER BY vc.p_vcontract) AS RowNum
    FROM vcontract vc
    INNER JOIN vco_partcp vp ON vc.p_vcontract = vp.p_vcontract 
    WHERE vc.p_vcontract NOT IN (SELECT p_vcontract FROM vco_object)
),
MaxValues AS (
    SELECT 
        COALESCE(MAX(p_vco_object), 0) AS MaxVcoObj,
        COALESCE(MAX(p_object), 0) - (SELECT COUNT(*) FROM MissingVcontracts) AS MaxObj
    FROM vco_object, object
)
INSERT INTO vco_object (p_vco_object, p_vcontract, p_object, create_date, create_user, last_date, last_user)
SELECT 
    mv.MaxVcoObj + mv.RowNum,
    mv.p_vcontract,
    mv.MaxObj + mv.RowNum,
    GETDATE(), 'MIG', GETDATE(), 'MIG'
FROM MissingVcontracts mv
CROSS JOIN MaxValues;

这种写法不需要游标，执行效率会高很多，尤其当vcontract表数据量较大时。

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