July 29, 2009 at 7:36 am
Hi,
I want to write a Procedure which calls itself.
I should also have control over the recursion loop, i.e. the 'procedure call loop' should be of finite number of times - say 10 times. The procedure should end after 10 loops (that is executing for 10 times)
Right now the Recursive Procedure I wrote is going into infinite loop, and I have no idea how to stop it after some loops.
Please help me!
Bhavesh
July 29, 2009 at 8:13 am
You could set the recursion level as an input parameter that the function passes to itself. You could set maxrecursion (see Books Online for details), but it should stop at the default on that, so that might not help.
Can you post the code of the function and any tables it queries? Then we can get more specific for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 8:22 am
How to use maxrecursion??
The procedure should store all the childrens of a parentID in a new table for a Binary Tree data.
The procedure is as shown below:
ALTER PROCEDURE [dbo].[USP_BinaryTreeChild]
(
@parentid INT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE@Child1 INT,
@Child2 INT
IF EXISTS (SELECT ID FROM tbl_tree1 WHERE ParentID=@parentid)
BEGIN
INSERT INTO tbl_tree2(ID, Name, ParentID, Age)
SELECT ID, Name, ParentID, Age FROM tbl_tree1 WHERE ParentID = @parentid
SET @Child1 = (select top 1 ID from tbl_tree1 where ParentID=@parentid)
SET @Child2 = (select top 2 ID from tbl_tree1 where ParentID=@parentid)
EXEC USP_BinaryTree @Child1
EXEC USP_BinaryTree @Child2
END
END TRY
BEGIN CATCH
PRINT @@ERROR
END CATCH
END
The Table with Binary Tree data is given below:
ID Name ParentID Age
1A021
2B125
3C125
4D226
5E227
6F330
7G319
8H418
9I424
10J537
11K526
12L624
13M628
14N721
15O720
July 29, 2009 at 8:35 am
Take a look at "Common Table Expression" in Books Online (or MSDN). There's a section on recursive CTEs, and it has a solution for what you're looking for here. More efficient and simpler.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 9:04 am
I don't think it can be solved by recursive CTE! I referred the MSDN site but it is not very helpful.
What I want is that procedure should return the child of a parentID as well as all the grand children. This is a Binary Tree Problem.
I am Looking for solution for above problem ..... but right now I am going no where!!!
Any one with with helping hands?? please help??
Bhavesh
July 29, 2009 at 9:42 am
Finally I got it .... Thanks every one
Bhavesh
July 29, 2009 at 4:31 pm
bhavesh_183 (7/29/2009)
Finally I got it .... Thanks every oneBhavesh
Got what? Please share your solution.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 29, 2009 at 10:38 pm
Here is my procedure code which solved my problem:
#############
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- --------------------------------------------------------------------------------------------------
-- Procedure : USP_BinaryTree
-- Parameters : @parentid
-- Description : Procedure to dipslay all the child IDs record for given parent IDs from tbl_tree1 table
-- Execute : EXEC USP_BinaryTree @parentid=1
-- Author : Bhavesh Patel
-- Create date : 29-07-2009
-- Edit History :
-- Copyright : Biztechnologies Pvt Ltd
-- --------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[USP_BinaryTree]
(
@parentid INT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN
WITH Tree_CTE AS
(
SELECTID, Name, ParentID, Age
FROMtbl_tree1
WHEREParentID=@parentid
UNION ALL
SELECTt.ID, t.Name, t.ParentID, t.Age
FROMtbl_tree1 t
INNER JOIN Tree_CTE tcte ON tcte.id = t.parentID
)
SELECT * FROM Tree_CTE OPTION (MAXRECURSION 10)
END
END TRY
BEGIN CATCH
PRINT @@ERROR
END CATCH
END
July 30, 2009 at 3:05 am
Check out @@NESTLEVEL in BOL
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 30, 2009 at 3:20 am
Hello Friends,
I have a new problem now.
I want the children count on left side and right side of the parent ID for the above binary tree (tbl_tree1).
Any way out??
Thanks,
Bhavesh
July 30, 2009 at 5:19 am
I got it guys .... Thanks every one
Bhavesh
July 30, 2009 at 4:44 pm
bhavesh_183 (7/30/2009)
I got it guys .... Thanks every oneBhavesh
Heh... have what? Share some code! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply