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. — dan radu
– dan radu, Commented Jul 11, 2012 at 2: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
– HABO, Commented Jul 11, 2012 at 2: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
– Axel, Commented Jul 11, 2012 at 4:07

Kevin Aenmey · Accepted Answer · 2012-07-11 02:36:57Z

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, 2012 at 2:36

answered Jul 11, 2012 at 2:28

Kevin Aenmey

13.4k5 gold badges48 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Axel Over a year ago

Even though it makes the code pretty unreadable, it works great! Thanks

azoppo · Accepted Answer · 2013-01-23 10:59:41Z

1

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

answered Jan 23, 2013 at 10:59

azoppo

1111 silver badge3 bronze badges

Collectives™ on Stack Overflow

MAXRECURSION value from local variable

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related