Another one: Recursive Stored Proc with cursor

[jluost1]

In my stored procedure, I created a local cursor, and loop through the cursor. Somewhere in the loop, I called (recursively) the Stored Procedure. I know this is crazy, but I cannot think of a better way.

When I run the stored procedure, it never finishes (I know it runs through quite a lot of recursion levels, but it should finish within minutes).

Following is my stored procedure (simplified):

CREATE PROCEDURE proc_add_access_to_all_dynamic_children

@title_id int

AS
declare @child_titleid int,
declare @has_child bit

DECLARE dynamic_children_cursor CURSOR
LOCAL
READ_ONLY
FOR
SELECT [id],has_child FROM title WHERE parent_id=@title_id

OPEN dynamic_children_cursor
FETCH NEXT FROM dynamic_children_cursor INTO @child_titleid, @has_child

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT title_id FROM user_access WHERE title_id=@child_titleid AND role_id=@role_id)
BEGIN
INSERT INTO user_access (title_id,role_id)
VALUES (@child_titleid, @role_id)

IF @has_child=1

EXEC proc_add_access_to_all_dynamic_children @child_titleid,@role_id

END

FETCH NEXT FROM dynamic_children_cursor

END

CLOSE dynamic_children_cursor
DEALLOCATE dynamic_children_cursor

 

[jjonesal]

I'm kinda new to SQL Server, so I may be way off base, but don't you need a RETURN statement somewhere to terminate the execution?

IF @has_child <> 1 RETURN

J. Jones
jjones@cybrtyme.com