MAXRECURSION value from local variable

Question

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?

Nothing is wrong, except that `MAXRECURSION` does not accept a variable, only a constant from 0 to 32767, unfortunately. See [query hints](http://msdn.microsoft.com/en-us/library/ms181714.aspx). — dan radu, Jul 11 '12 at 02:24
Could you post more of the CTE so we can see why it loops forever? There might be a more graceful way of handling `@bar = NULL`. Or simply use an `if` to execute one of two queries, one with the full CTE and the other with only the initial `select` but no `union`. — HABO, Jul 11 '12 at 02:55
Yeah that's exactly what I ended up doing (using a big `IF...ELSE` statement). The code of the SP ends up being almost twice as long, but it's still more readable than having the entire code in red. — Axel, Jul 11 '12 at 04:07

Kevin Aenmey · Accepted Answer · 2012-07-11T02:36:57.153

3

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.

edited Jul 11 '12 at 02:36

answered Jul 11 '12 at 02:28

Kevin Aenmey

13,259
5
46
45

Even though it makes the code pretty unreadable, it works great! Thanks – Axel Jul 11 '12 at 03:04

score 1 · Answer 2 · answered Jan 23 '13 at 10:59

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

MAXRECURSION value from local variable

2 Answers2

Linked