Search This Blog

2010-03-18

Get all Child IDs from Self Referential Table(DFS)

When you have a self referential table as follows,



and you need a stored proc which will take the parent key and return all the Childs and Sub Childs ID's along with the parent key.Like

if you supply :2

it will return 2,4,5,6,7,8 and

if you supply :5

it will return :5,7,8

In this context, you can create the following SP

Stored Proc:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FnGetChildIDs]
@KEYID INT,
@IDCOL VARCHAR(20),
@IDVAL VARCHAR(20),
@PIDCOL VARCHAR(20),
@TABLENAME VARCHAR(200)
AS

DECLARE @STR VARCHAR(2000)
Begin
SET @STR= 'WITH RECURSIONCTE(' + @IDCOL +',CATEGORYDESCRIPTION)
AS
(
SELECT '+@IDCOL +','+@IDVAL +'
FROM ' + @TABLENAME +'
WHERE ' +@IDCOL +'='+ CAST(@KEYID AS VARCHAR) +
' UNION ALL
SELECT R1.' +@IDCOL+',
R1.'+@IDVAL +
' FROM '+@TABLENAME + ' AS R1
JOIN RECURSIONCTE AS R2 ON R1.'+@PIDCOL +'=R2.'+@IDCOL+'
)
SELECT '+ @IDCOL +' FROM RECURSIONCTE'
PRINT(@STR)
EXEC (@STR)

End


if @@Error <> 0
Begin
Raiserror ('GetChildIDs',16,1)
End


Table Schema
CREATE TABLE [dbo].[tblProductMaster](
[ProductID] [bigint] NOT NULL,
[Name] [varchar](50) NULL,
[ParentID] [bigint] NULL,
CONSTRAINT [PK_tblProductMaster] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Execute the SP:
EXEC FNGETCHILDIDS 2,'PRODUCTID','NAME','PARENTID','DBO.TBLPRODUCTMASTER'