問題描述

我正在 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 的參數(shù)之一 (@bar) 為空時無限循環(huán).

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

為了停止這個無限循環(huán)，我一直在嘗試使用選項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，它根據(jù) @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)

當我解析代碼時，出現(xiàn)以下錯誤:'@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.

這篇關于來自局部變量的 MAXRECURSION 值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！