T-SQL Stored Procedure for Looping a hierarchy with recursion

i have a specific Question.

My Stored Procedure don't work correctly.. I will show you the Procedure, the database and the parameter:

ALTER PROCEDURE [dbo].[ManufacturerParentToChild] 

@ServiceProviderId int,
@CarmakerId int

AS BEGIN

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 > 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

You See my stored Procedure above.

The Database Table Service Provider have 7 Rows:

ServiceProviderId Name ParentServiceProviderId isDeleted

1 'Parent' Null 0

2 'Child1' 1 0

3 'Child2' 1 0

4 'Child4' 2 0

5 'Child5' 3 0

6 'child6' 4 0

7 'Child7' 6 0

The Paramater get the Values:

@ServiceProviderId = 1 @CarmakerId = 5

The Pr开发者_如何学Goocedure insert the ServiceProviderId 7, 6, 4 and 2 into CarmakerPartnerShip but don't insert 3 and 5!

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 ??

Thank you for your Help and Sorry for my bad english!!

If you have Questions you can ask me.

Best Regards

Alex

The fetch statement in the while block may be the cause. Currently it reads:

FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId;

try replacing it with:

FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId, @isDeleted;