Help handling infinite loop in recursive function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help handling infinite loop in recursive function

Author

Topic

fezzik
Starting Member

7 Posts

Posted - 2008-10-09 : 18:37:40

I have the following recursive function which outputs a delimited list of ids.


ALTER FUNCTION [dbo].[fn_GetLeads] (
	@id int
) 
RETURNS varchar(MAX) AS

BEGIN

DECLARE 
	@rowID int,
	@tmp varchar(MAX),
	@ret varchar(MAX),
	@leadID int

SELECT @ret = ''

SELECT @rowID = MIN(ID) FROM [Delegates] WHERE DelegateID = @id
				
WHILE @rowID IS NOT NULL
	BEGIN
		SET @leadID = (SELECT LeadID FROM [Delegates] WHERE ID = @rowID)
		SELECT @ret = CAST(@leadID AS varchar(12)) + ' ' + @ret		

		EXEC @tmp = dbo.fn_GetLeads @leadID		
		
		SELECT @ret = CAST(@tmp AS varchar(MAX)) + @ret	
		
		SELECT @rowID = MIN(ID) FROM [Delegates] WHERE DelegateID = @id AND ID > @rowID
	END

RETURN @ret


END

I'm encountering the issue where if a delegate is the lead of a pre-existing delegate the function is caught in an endless loop. I need assistance with creating a method to check if the delegate id already exists within the list and if so, it is skipped/ignored.

My delegate table:

CREATE TABLE [dbo].[Delegates](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LeadID] [int] NULL,
[DelegateID] [int] NULL,
)

Any assistance is appreciated.

Best,

Fezzik

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-10 : 00:33:32

Can U post some sample input data and desired output.

fezzik
Starting Member

7 Posts

Posted - 2008-10-10 : 11:32:47

Peter,

Sample data:

Delegates
ID LeadID DelegateID
1 4 18
2 2 18
3 18 2
4 13 1
5 14 1

dbo.fn_GetLeads 1
Resulting output = "13 14"

Where as
dbo.fn_GetLeads 18
Results in an infinite loop

I'd like the desired output of
dbo.fn_GetLeads 18
Resulting output = "2 4"

Please let me know if you need anymore information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 12:05:29

please explain how the output given comes for your sample data posted before?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources