Parent Child Ids (Infinite Depth)

Input format:


Output:

CREATE TABLE [dbo].[tblSkillSet](

[SkillId] [int] IDENTITY(1,1) NOT NULL,

[SkillName] [varchar](250) NOT NULL,

[ParentSkillId] [int] NOT NULL CONSTRAINT [DF_tblSkillSet_ParentSkillId] DEFAULT (0),

[Depth] [int] NULL,

[Lineage] [varchar](100) NULL,

CONSTRAINT [PK_tblSkillSet] PRIMARY KEY CLUSTERED

(

[SkillId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--------------------------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SelALLParentsAndChilds]

AS

SET NOCOUNT ON

Create table #tblSkills(SkillId INT, SkillName VARCHAR(8000))

CREATE TABLE #stack (Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

CREATE TABLE #stack1 (SkillId INT ,Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

DECLARE @maxId INT

DECLARE @strskill VARCHAR(8000)

DECLARE @colskill VARCHAR(8000)

DECLARE @parentId INT

DECLARE @level INT, @line char(20)

Declare @SKillName VARCHAR(50)

SET @strskill=''

DECLARE @PSID INT

DECLARE @OuterPSID INT

DECLARE @PSName VARCHAR(100)

DECLARE cur_skill1 cursor for SELECT SkillId from tblSkillSet WHERE ParentSkillId = 0

OPEN cur_skill1

FETCH next from cur_skill1 into @PSID

SET @OuterPSID = @PSID

WHILE @@FETCH_STATUS=0

BEGIN

DELETE FROM #stack1

SELECT @SkillName =SkillName from tblSkillSet WHERE ParentSkillId =@PSID

INSERT INTO #stack VALUES (@PSID, @SkillName, 1)

SELECT @level = 1

WHILE @level > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE [level] = @level)

BEGIN

SELECT @PSID = Parent_Id FROM #stack WHERE [level] = @level

SELECT @line = space(@level - 1) + @PSID

PRINT @line

DELETE FROM #stack WHERE [level] = @level AND parent_id = @PSID

INSERT #stack

SELECT SkillId , SkillName , @level + 1 FROM tblSkillSet WHERE ParentSkillId = @PSID

DECLARE @strinnerSkillName VARCHAR(100)

SET @strinnerSkillName = ''

IF exists (SELECT * FROM #stack1 WHERE SkillId = @PSID)

BEGIN

SET @strinnerSkillName = (SELECT SkillName from #stack1 WHERE SkillId=@PSID) + '>>'

INSERT #stack1

SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName, @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

ELSE

BEGIN

SET @strinnerSkillName = (SELECT max(SkillName) from tblSkillSet WHERE SkillId=@PSID) + '>>'

INSERT #stack1 SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName , @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

IF @@ROWCOUNT > 0

BEGIN

SELECT @level = @level + 1

END

IF exists ( SELECT 'sometext' from #stack WHERE parent_id=@PSID)

SELECT @level=0

END

ELSE

SELECT @level = @level - 1

END -- WHILE

SET @PSName = (SELECT skillName from tblSkillSet WHERE SkillId=@OuterPSID)

INSERT INTO #tblSkills VALUES(@OuterPSID,@PSName)

INSERT INTO #tblSkills SELECT skillId,skillName from #stack1

FETCH next FROM cur_skill1 INTO @PSID

SET @OuterPSID = @PSID

END --for cursor

CLOSE cur_skill1

DEALLOCATE cur_skill1

SELECT * FROM #tblSkills

--------------------------------------------------------------------------------------------------------------------------