



CREATE PROCEDURE DBO.AllDownLinksAgency AS
SET NOCOUNT ON

 Create Table #Temp1 (CustomerNumber int NOT NULL,
                                              CustomerTypeNumber int NOT NULL)

Insert #Temp1(CustomerNumber,CustomerTypeNumber)
  Select CustomerNumber,CustomerTypeNumber from Customer WITH (NOLOCK) where  (CustomerTypeNumber=1 or CustomerTypeNumber=2 or CustomerTypeNumber=6) And CustomerNumber In
   (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=3 ) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in  (select CustomerNumber from #FoundValues where CustomerTypeNumber=3))
  
  Insert #FoundValues(CustomerNumber,CustomerTypeNumber)
  select CustomerNumber,CustomerTypeNumber from Customer WITH (NOLOCK) where CustomerTypeNumber=1 and CustomerNumber in
  (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select  CustomerNumber from #Temp1 where CustomerTypeNumber=2 ) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and  CustomerNumber in  (select  CustomerNumber from #Temp1 where CustomerTypeNumber=2))
  
  Insert #FoundValues(CustomerNumber,CustomerTypeNumber)
  Select * from #Temp1

DROP TABLE #Temp1

