USE [chk]
GO
/****** Object:  Table [dbo].[Demo]    Script Date: 12/05/2011 15:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Demo](
	[AC_ID] [bigint] NOT NULL,
	[RootId] [bigint] NULL,
	[parentID] [bigint] NULL,
	[Type] [varchar](50) NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [demo] VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO [demo] VALUES(12,3,3,'S','SubGroup31')
INSERT INTO [demo] VALUES(13,4,4,'S','SubGroup41')
INSERT INTO [demo] VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO [demo] VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO [demo] VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO [demo] VALUES(17,1,5,'A','AC11')
INSERT INTO [demo] VALUES(18,1,5,'A','AC12')
INSERT INTO [demo] VALUES(19,1,7,'A','AC13')
INSERT INTO [demo] VALUES(20,1,8,'A','AC14')
INSERT INTO [demo] VALUES(21,2,9,'A','AC21')
INSERT INTO [demo] VALUES(22,2,9,'A','AC22')
INSERT INTO [demo] VALUES(23,2,11,'A','AC23')
INSERT INTO [demo] VALUES(24,2,11,'A','AC24')

ALTER PROC [dbo].[ShowHierarchy]
@Root int



as

	DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)

	SET @ACName = (SELECT [Name]  FROM  dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType1 = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--'  --+  @root
	SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root)


	WHILE @ACID IS NOT NULL
	BEGIN
		EXEC dbo.ShowHierarchy @ACID
		SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root  AND AC_ID > @ACID )
	END

----Group1--
--------SubGroup11--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
------------AC11--
------------AC12--
--------SubGroup12--

----Group1--
--------SubGroup11--
------------AC11--
------------AC12--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
--------SubGroup12--

quote:

---

Originally posted by visakh16

why not use a recursive cte for this rather than calling sp repeatedly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---