Recurssive look through record history to find the wanted record

I'm new to CTE and I am trying to figure this out. I have a table that stores all contracts I have the record of the most current contract "Contract A" and i want to go back through the related contracts in historical order to find the latest contract that had a specific criteria, sometimes it will be the contract i already have. Sometime one level deep and maybe up to 5 levels deep. i built a CTE and it limited the records to the records associ开发者_运维百科ated with "Contract A" but it goes into an infinite loop and sql kills it BTW i am using SQL Server 2008 Here is the code i have:

CREATE TABLE #t_Contracts
(   ContractOID INT NOT NULL
,   SystemSourceOID SMALLINT NOT NULL
)

--"Contract A"
INSERT #t_Contracts
SELECT 7463027, 2


WITH Contract_CTE (ContractOID, SystemSourceOID, PrevContractOID, ProductCodeType,Comment, Contractlevel)
AS
(
    --Anchor with "Contract A"
    SELECT  CON.ContractOID, CON.SystemSourceOID, CON.PrevContractOID, PRD.ProductCodeType, PRD.Comment, 0 AS Contractlevel
    FROM    UBASS.dbo.[Contract]    AS CON 
    INNER JOIN #t_Contracts         AS TMP  ON  CON.ContractOID = TMP.ContractOID
                                            AND CON.SystemSourceOID = TMP.SystemSourceOID
    INNER JOIN UBASS.dbo.Product    AS PRD  ON  CON.ProductOID = PRD.ProductOID 
                                            AND CON.SystemSourceOID = PRD.SystemSourceOID   
    UNION ALL
    --"Contract A" Previous contracts
    SELECT CON.ContractOID, CON.SystemSourceOID, CON.PrevContractOID, PRD.ProductCodeType, PRD.Comment, Contractlevel + 1
    FROM        UBASS.dbo.[Contract]    AS CON
    INNER JOIN  Contract_CTE    AS pCON ON  pCON.PrevContractOID = CON.ContractOID
                AND CON.SystemSourceOID = pCON.SystemSourceOID
    INNER JOIN UBASS.dbo.Product    AS PRD  ON  CON.ProductOID = PRD.ProductOID 
                                            AND CON.SystemSourceOID = PRD.SystemSourceOID

)



SELECT * FROM   Contract_CTE
WHERE 
ProductCodeType NOT IN ('MPP','RBP','STP','RCP','BNE')
AND Comment != 'RETENTION'

--WHERE ContractOID = 7463027 AND SystemSourceOID = 2

So This code worked as intended it was all Data Issues. with it resolved it works great!