title: T-SQL Stored Procedure for Looping a hierarchy with recursion
body: <p>i have a specific Question.</p>
  
  <p>My Stored Procedure don't work correctly.. I will show you the Procedure, the database and the parameter:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[ManufacturerParentToChild] 
  
  @ServiceProviderId int,
  @CarmakerId int
  </code></pre>
  
  <p>AS
  BEGIN</p>
  
  <pre><code>SET NOCOUNT ON;
  
  DECLARE @childSPPId int, @isDeleted bit
  
  DECLARE ServiceProviderChildren_Cursor CURSOR FOR
  
  SELECT ServiceProviderId, isDeleted
  FROM ServiceProvider 
  WHERE ParentServiceProviderId = @ServiceProviderId; 
  
  OPEN ServiceProviderChildren_Cursor;
  
  FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId, @isDeleted;
  
  WHILE @@FETCH_STATUS = 0
     BEGIN
          IF @ServiceProviderId &gt; 0
          BEGIN
              EXEC ManufacturerParentToChild @childSPPId, @CarmakerId;
  
              IF (SELECT COUNT(*) FROM dbo.CarmakerPartnership WHERE ServiceProviderId = @childSPPId AND CarmakerId = @CarmakerId) = 0
              BEGIN
                  IF (@isDeleted = 0)
                  BEGIN
                      INSERT INTO dbo.CarmakerPartnership (CarmakerId, ServiceProviderId, CreatedBy, ChangedBy, ValidityPeriodFrom, ValidityPeriodTo) VALUES (@CarmakerId, @childSPPId, SYSTEM_USER, SYSTEM_USER, '01.01.1900 00:00:00', '31.12.9999 23:59:00.000')
                  END
              END 
          END
        FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId;
     END;
  CLOSE ServiceProviderChildren_Cursor;
  DEALLOCATE ServiceProviderChildren_Cursor; END
  </code></pre>
  
  <p>You See my stored Procedure above.</p>
  
  <p>The Database Table Service Provider have 7 Rows:</p>
  
  <p>ServiceProviderId Name ParentServiceProviderId isDeleted<br>
  1 'Parent' Null 0 <br/>
  2 'Child1' 1 0 <br/>
  3 'Child2' 1 0 <br/>
  4 'Child4' 2 0 <br/>
  5 'Child5' 3 0 <br/>
  6 'child6' 4 0 <br/>
  7 'Child7' 6 0 <br/></p>
  
  <p>The Paramater get the Values:</p>
  
  <p>@ServiceProviderId = 1
  @CarmakerId = 5</p>
  
  <p>The Procedure insert the ServiceProviderId 7, 6, 4 and 2 into CarmakerPartnerShip but don't insert 3 and 5!</p>
  
  <p>Have anybody a idea why the recursive loop jump trough the childs of ServiceProviderId = 2 but don't jump trough the childs of ServiceProviderId = 3 ??</p>
  
  <p>Thank you for your Help and Sorry for my bad english!!</p>
  
  <p>If you have Questions you can ask me.</p>
  
  <p>Best Regards </p>
  
  <p>Alex</p>