来自局部变量的 MAXRECURSION 值

2021-09-10 00:00:00 sql tsql sql-server

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

I'm writing a Stored Procedure in SQL Server 2005 that declares a CTE (Common Table Expression) called foo.

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

foo calls itself recursively, but loops infinitely when one of the SP's parameters (@bar) is null.

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

To stop this infinite loop, I've been trying to use the option MAXRECURSION:

  • 当@bar为空时，设置MAXRECURSION为1；
  • 当 @bar 不为空时，将 MAXRECURSION 设置为 0(无限制).
  • when @bar is null, set MAXRECURSION to 1;
  • when @bar is not null, set MAXRECURSION to 0 (no limit).

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

So I've declared a local variable @maxrec that takes 1 or 0 depending on whether @bar is null or not.

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).

When I parse the code, I get the following error: Incorrect syntax near '@maxrec'., which refers to the line OPTION (MAXRECURSION @localvar).

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

So what am I doing wrong? Is it forbidden to use a local variable within an OPTION clause?

推荐答案

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

One option would be to build the query and then execute it with 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 值，查询将不会正常结束，它将抛出异常.这可能就是您想要的，但请注意这一点.

On a side note, if the MAXRECURSION value is reached before the statement completes, the query will not end gracefully, it will throw an exception. That may be what you want, but just be aware of it.

相关文章