問(wèn)題描述

我正在 SQL Server 2005 中編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，它聲明了一個(gè)名為 foo 的 CTE(公用表表達(dá)式).

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

foo 遞歸調(diào)用自身，但當(dāng) SP 的參數(shù)之一 (@bar) 為空時(shí)無(wú)限循環(huán).

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

為了停止這個(gè)無(wú)限循環(huán)，我一直在嘗試使用選項(xiàng)MAXRECURSION:

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

• 當(dāng)@bar為空時(shí)，設(shè)置MAXRECURSION為1；
• 當(dāng) @bar 不為空時(shí)，將 MAXRECURSION 設(shè)置為 0(無(wú)限制).

• when @bar is null, set MAXRECURSION to 1;
• when @bar is not null, set MAXRECURSION to 0 (no limit).

所以我聲明了一個(gè)局部變量 @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)

當(dāng)我解析代碼時(shí)，出現(xiàn)以下錯(cuò)誤:'@maxrec' 附近的語(yǔ)法不正確.，指的是行 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).

那我做錯(cuò)了什么?是否禁止在 OPTION 子句中使用局部變量?

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

推薦答案

一種選擇是構(gòu)建查詢，然后使用 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

附帶說(shuō)明，如果在語(yǔ)句完成之前達(dá)到 MAXRECURSION 值，查詢將不會(huì)正常結(jié)束，它將拋出異常.這可能就是您想要的，但請(qǐng)注意這一點(diǎn).

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.

這篇關(guān)于來(lái)自局部變量的 MAXRECURSION 值的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！