How to use iteration in SQL Server?

Question

I need to find to every credit advisor his/her area manager.

Our network looks like:

CareerSystemPositionId  CareerSystemPositionCode_Primary    PositionShortcut    OriginalPositionShortcut    PositionShortcutTranslationId   
2   -1  PM  PM  30  Profi Manager 
3   0   RM  RM  31  Region Manager
4   1   AM  AM  32  Area Manager 
5   2   TM  TM  33  Team Leader
6   3   CAS ÚPS 34  Senior Credit Advisor 
7   4   CA  ÚP  35  Senior Credit

I've tried:

-- Iterate until encountering AM position ID
:WAY

;WITH CA AS 
(
    SELECT
        ca.CreditAdvisorId AS CA_ID,
        ca.CreditAdvisorCode_Primary AS CA_Code,
        ca.CreditAdvisorParentID AS ParentID,
        ca.CareerSystemPositionId AS PositionID,
        ca.ClosestManagerId AS CloseManID
    FROM 
        dim.CreditAdvisor ca
    WHERE 
        ca.CreditAdvisorId = @CA_id --nenacitame vsechny zaznamy
)
SET @CA_id = (SELECT ParentID FROM CA)

IF NOT (CA.PositionID <= 4) GOTO WAY;

-- Final function result
SELECT
    IIF(ca.PositionID = 4, ca.CA_ID, 0) AS AM_Id, -- when no AM in a network, return 0
    IIF(ca.PositionID = 4, ca.CA_Cod, 0) AS AM_code
FROM
    CA ca

Any help would be appreciated.

Thanks in advance

SQL is not an imperative language. It deals in tabular values. You need to use [recursion](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) — Caleth, Sep 06 '17 at 13:56

score 0 · Answer 1 · answered Sep 06 '17 at 14:05

You need to specify your source as recursive table expression, then select the data you want from it

WITH CA AS 
(
    SELECT
        ca.CreditAdvisorId AS CA_ID,
        ca.CreditAdvisorCode_Primary AS CA_Code,
        ca.CreditAdvisorParentID AS ParentID,
        ca.CareerSystemPositionId AS PositionID,
        ca.ClosestManagerId AS CloseManID
    FROM 
        dim.CreditAdvisor ca
    WHERE 
        ca.CreditAdvisorId = @CA_id --nenacitame vsechny zaznamy

    UNION ALL

    SELECT
        dca.CreditAdvisorId AS CA_ID,
        dca.CreditAdvisorCode_Primary AS CA_Code,
        dca.CreditAdvisorParentID AS ParentID,
        dca.CareerSystemPositionId AS PositionID,
        dca.ClosestManagerId AS CloseManID
    FROM 
        dim.CreditAdvisor dca
    JOIN CA ON dca.CreditAdvisorId = CA.ParentID
    WHERE
        CA.PositionID <= 4
)
SELECT
    ca.CA_ID AS AM_Id
    ca.CA_Code AS AM_code
FROM
    CA ca
WHERE
    ca.PositionId = 4;

How to use iteration in SQL Server?

1 Answers1