troubleshooting Question

CTE Query limit max recursion without exception

Avatar of Compmind
Compmind asked on
Microsoft SQL Server 2005SQL
4 Comments1 Solution1012 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros