hi
this is my demo table
USE [chk]
GO
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
this is my demo data
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')
and this is my procedure
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+'--'
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
whn i run this procedure
like
--exec ShowHierarchy 1
so i getting result like this
but i want result like that
Plese Help Me....