sql - 来自局部变量的 MAXRECURSION 值

Question

我正在 SQL Server 2005 中编写一个存储过程，它声明一个名为 foo 的 CTE(公用表表达式)。

foo 递归调用自身，但当 SP 的参数之一 (@bar) 为空时无限循环。

为了停止这个无限循环，我一直在尝试使用选项 MAXRECURSION:

• 当@bar为null时，设置MAXRECURSION为1；
• 当 @bar 不为空时，将 MAXRECURSION 设置为 0(无限制)。

所以我声明了一个局部变量 @maxrec，它根据 @bar 是否为 null 取值 1 或 0。

DECLARE @maxrec INT;
SET @maxrec = 0;
if (@dim_course_categories is null)
begin
    SET @maxrec = 1;
end

;WITH foo AS (
    ...
)

SELECT * FROM foo
OPTION (MAXRECURSION @maxrec)

当我解析代码时，出现以下错误:“@maxrec”附近的语法不正确。，它指的是 OPTION (MAXRECURSION @localvar) 行。

那我做错了什么？是否禁止在 OPTION 子句中使用局部变量？

Answer 1

一种选择是构建查询，然后使用 EXEC sp_executesql 执行它

DECLARE @Query NVARCHAR(MAX)

SET @Query = N'
    ;WITH foo AS (
        ...
    )

    SELECT * FROM foo
    OPTION (MAXRECURSION ' + CAST(@maxrec AS NVARCHAR) + ');'

EXEC sp_executesql @Query

附带说明，如果在语句完成之前达到 MAXRECURSION 值，查询将不会正常结束，它会抛出异常。这可能是您想要的，但请注意这一点。