Link to home
Create AccountLog in
Avatar of a222493
a222493

asked on

How do I Print a Heirarchy using Recursion?

Hi,
I have a table of data that has a PortfolioID column and a ParentPortfolioID column (FK back into the same table) which create a hierarchy.  I wrote a stored procedure to display the data hierarchically.  Each level of nodes is sorted by the PortfolioID field.  

I just received a requirement to sort them by the Tal_TargPercent column.  Can anyone suggest modifications for the stored procedure?  Id rater not use a cursor if I can avoid it&

-- Table definition
CREATE TABLE [dbo].[Portfolio](
	[PortfolioID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](60) NOT NULL,
	[Tal_TargPercent] [real] NULL,
	[ParentPortfolioID] [int] NULL)
 
ALTER PROC [dbo].[uspPrintHierarchyByAC]
(
	@Root int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @ID int, @Name varchar(30)
 
	SET @Name = (SELECT [Name] FROM dbo.vPortfolioHeirByAC WHERE [PortfolioID] = @Root)
	PRINT '  ' + REPLICATE('-', @@NESTLEVEL * 4) + @Name
 
	SET @ID = (SELECT MIN([PortfolioID]) FROM dbo.vPortfolioHeirByAC WHERE [ParentPortfolioID] = @Root)
 
	WHILE @ID IS NOT NULL
	BEGIN
		EXEC dbo.uspPrintHierarchyByAC @ID
		SET @ID = (SELECT MIN([PortfolioID]) FROM dbo.vPortfolioHeirByAC WHERE [ParentPortfolioID] = @Root AND [PortfolioID] > @ID)
	END
END

Open in new window

Avatar of climbingjaffa
climbingjaffa
Flag of Ireland image

Look into using CTE's we have used these for similar scenarios .... This article seems pretty good
http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/
Avatar of a222493
a222493

ASKER

Hi,

Thanks for the response.  However, I am not able to see how using the CTE will help my problem.  Can you provide more direction?
Avatar of a222493

ASKER

Anyone?
ASKER CERTIFIED SOLUTION
Avatar of a222493
a222493

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hey sorry for the late reply when we try to do hierachial processing and we wish to avoid Cursors we use recursive CTE's ..... I think i got hung up on the without using the cursor part of the question my bad
Avatar of modus_operandi
Closed, 500 points refunded.
modus_operandi
EE Moderator