CTE Query limit max recursion without exception

How can I limit the recursion level of a CTE without getting an exception? I know of
option (maxrecursion numberofrows) but that gets me the message

"The statement terminated. The maximum recursion 2 has been exhausted before statement completion." which ends up being used in the results for a third party software I am using. Anyway way I can supress this exception? (or any other way to limit this)

Thanks in advance

With cteCategories
AS (
Select contact_uuid,(first_name + ' ' + last_name) as fName,supervisor_contact_uuid,contact_type,userid
From ca_contact
Where upper(userid) = upper('yy4456') and inactive = 0
 
Union All
 
Select C.contact_uuid,(C.first_name + ' ' + C.last_name) as fName,C.supervisor_contact_uuid,C.contact_type,C.userid
From ca_contact As C Inner Join cteCategories As P On C.supervisor_contact_uuid = P.contact_uuid
 
)
 
Select fName,contact_uuid From cteCategories Where upper(userid) <> upper('yy4456') and (contact_type = 2305 or contact_type = 2307)

Open in new window

Microsoft SQL Server 2005SQL

Last Comment

Compmind

8/22/2022 - Mon

Guy Hengel [angelIII / a3]

10/6/2009

this should do:

With cteCategories
AS (
Select 0 as [level], contact_uuid,(first_name + ' ' + last_name) as fName,supervisor_contact_uuid,contact_type,userid
From ca_contact
Where upper(userid) = upper('yy4456') and inactive = 0
 
Union All
 
Select p.[level] + 1C.contact_uuid,(C.first_name + ' ' + C.last_name) as fName,C.supervisor_contact_uuid,C.contact_type,C.userid
From ca_contact As C Inner Join cteCategories As P On C.supervisor_contact_uuid = P.contact_uuid
WHERE p.[level] <= 10 
)
 
Select fName,contact_uuid From cteCategories Where upper(userid) <> upper('yy4456') and (contact_type = 2305 or contact_type = 2307)