使用递归循环层次结构的T-SQL存储过程 [英] T-SQL Stored Procedure for Looping a hierarchy with recursion
问题描述
我有一个具体的问题.
我的存储过程无法正常工作..我将向您显示过程,数据库和参数:
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 开始
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.
数据库表服务提供者有7行:
The Database Table Service Provider have 7 Rows:
ServiceProviderId名称ParentServiceProviderId已删除
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
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
参数获取值:
@ServiceProviderId = 1 @CarmakerId = 5
@ServiceProviderId = 1 @CarmakerId = 5
该过程将ServiceProviderId 7、6、4和2插入CarmakerPartnerShip中,但不要插入3和5!
The Procedure insert the ServiceProviderId 7, 6, 4 and 2 into CarmakerPartnerShip but don't insert 3 and 5!
任何人都知道为什么递归循环跳过ServiceProviderId = 2的子代,却不跳过ServiceProviderId = 3的子代吗?
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.
最好的问候
亚历克斯
推荐答案
while块中的fetch语句可能是原因.目前显示为:
The fetch statement in the while block may be the cause. Currently it reads:
FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId;
尝试将其替换为:
FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId, @isDeleted;
这篇关于使用递归循环层次结构的T-SQL存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!