MAXRECURSION value from local variable

问题:

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

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

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

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

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)

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

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

回答1:

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

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.

回答2:

Another option is change your select

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

;WITH foo AS (
    SELECT colonne
    UNION ALL
    SELECT colonne FROM foo
    WHERE @maxrec = 0
      AND (other_condition)
)

SELECT * FROM foo
OPTION (MAXRECURSION 0);

Alberto