How to track recursive point where is exactly causing in sql ?

Question

0.00/5 (No votes)

See more:

Hi ,

I have database , in which employee manager relationship is happening somewhere which is causing max recurssion limit

for example A->B->c->A (Employee manager relationship) now i have so many companies and so many employees in that database ..

basically i am trying for each and everycompany from below code

SQL

Declare
 @compID as int=45,
 @roleName as varchar(50) = 'manager'


Begin
WITH CTE_Emp(Id, EmpName, ManagerID, [Level], RoleName) AS
   (
    SELECT
     t1.Id,
     t1.FirstName + ' ' + t1.LastName 'EmpName',
     t1.ManagerId,
     1,
     (SELECT r1.Name FROM [Role] as r1 with (nolock) WHERE r1.Id = t1.Role_Id) 'RoleName'
    FROM Employee as t1 with (nolock)
    WHERE t1.Id  in (select e.Id from Employee as e with (nolock)) AND t1.IsDeleted = 0 AND t1.Status = 1 AND t1.Company_Id = @compID

    UNION ALL

    SELECT
     t1.Id,
     t1.FirstName + ' ' + t1.LastName 'EmpName',
     t1.ManagerId,
     (t2.[Level] + 1),
     (SELECT r1.Name FROM [Role] as r1 with (nolock) WHERE r1.Id = t1.Role_Id) 'RoleName'
    FROM Employee as t1 with (nolock)
    INNER JOIN CTE_Emp as t2 on t1.ManagerId = t2.Id
    WHERE t1.IsDeleted = 0 AND t1.[Status] = 1 AND t1.Company_Id = @compID
   )

select Id, EmpName, ManagerID, [Level], RoleName from CTE_Emp where RoleName = ISNULL(@roleName, RoleName)

End

GO

is there is any way from which i can pass all commpany id and track down on which companyId it is causing an issue .. and then i will check for perticular company employee where again is there is any way where i can send all employee to check and can be able to track down on which employee is caussing recurssion problem ??

Posted 3-Jun-15 2:35am

Torakami

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2015-06-03T04:22:00

Something like this should work:

SQL

WITH cteData (Id, ManagerId, EmployeePath, IsLoop) As
(
    SELECT
        Id,
        ManagerId,
        '/' + CAST(Id As varchar(max)) + '/',
        CASE
            WHEN ManagerId = Id THEN CAST(1 As bit)
            ELSE CAST(0 As bit)
        END
    FROM
        Employee

    UNION ALL

    SELECT
        D.Id,
        E.ManagerId,
        D.EmployeePath + CAST(E.Id As varchar(max)) + '/',
        CASE
            WHEN D.EmployeePath Like '%' + CAST(E.Id As varchar(max)) + '%' THEN CAST(1 As bit)
            ELSE CAST(0 As bit)
        END
    FROM
        cteData As D
        INNER JOIN Employee As E
        ON E.Id = D.ManagerId
    WHERE
        D.IsLoop = 0
)
SELECT
    Id,
    EmployeePath
FROM
    cteData
WHERE
    IsLoop = 1
;

That will give you the list of all employees which are part of a loop, with the list of employee IDs that form the loop.

EDIT:
Based on the suggestion posted by Jörgen Andersson in the comments, this should give you the same results with better performance:

SQL

WITH cteData (Id, ManagerId, EmployeePath, Distance) As
(
    SELECT
        Id,
        ManagerId,
        CAST(Id As varchar(max)),
        0 As Distance
    FROM
        Employee

    UNION ALL

    SELECT
        D.Id,
        E.ManagerId,
        D.EmployeePath + ' > ' + CAST(E.Id As varchar(max)),
        D.Distance + 1
    FROM
        cteData As D
        INNER JOIN Employee As E
        ON E.Id = D.ManagerId
    WHERE
        D.ManagerId != D.Id
    And
        E.ManagerId != E.Id
)
SELECT
    Id,
    EmployeePath,
    Distance
FROM
    cteData
WHERE
    ManagerId = Id
And
    Distance != 0
;