Sample for recursive CTE




IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tmpCategory'))
BEGIN
CREATE TABLE [dbo].[tmpCategory](
[nCategoryID] [int] IDENTITY(1,1) NOT NULL,
[strCategoryName] [varchar](250) NULL,
[nCategoryParentID] [int] NULL,
[bLeafCategory] [bit] NULL,
[nCatUpdateId] [int] NULL,
[strGroupId] [varchar](15) NULL );

INSERT INTO dbo.[tmpCategory]([strCategoryName],[nCategoryParentID],[bLeafCategory] ) VALUES('1',null,null);
INSERT INTO dbo.[tmpCategory]([strCategoryName],[nCategoryParentID],[bLeafCategory] ) VALUES('2',1,null);
INSERT INTO dbo.[tmpCategory]([strCategoryName],[nCategoryParentID],[bLeafCategory] ) VALUES('3',2,null);
INSERT INTO dbo.[tmpCategory]([strCategoryName],[nCategoryParentID],[bLeafCategory] ) VALUES('4',2,1);
INSERT INTO dbo.[tmpCategory]([strCategoryName],[nCategoryParentID],[bLeafCategory] ) VALUES('5',3,1);

END

go

Create FUNCTION [dbo].[GetChildCategory]
(
@nCategoryId int
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
with [CTE] as (
select nCategoryId, strCategoryName, nCategoryParentId from dbo.[tmpCategory] c (nolock) where c.nCategoryParentId = @nCategoryId and c.nCategoryId != @nCategoryId
union all
select c.nCategoryId, c.strCategoryName, c.nCategoryParentId from [CTE] p, dbo.[tmpCategory] c (nolock) where c.nCategoryParentId = p.nCategoryId
)
select nCategoryId from [CTE]
union all
select nCategoryId from dbo.tmpCategory c (nolock) where c.nCategoryId = @nCategoryId
)

go


Create FUNCTION [dbo].[GetTopCategory]
(
@nCategoryId INT
)
RETURNS TABLE
AS
RETURN
(
WITH TopCategory(nCategoryID, strCategoryName, nCategoryParentID, bLeafCategory, strGroupID)
AS
(SELECT nCategoryID, strCategoryName, nCategoryParentID, bLeafCategory, strGroupID FROM dbo.tmpCategory WHERE nCategoryID = @nCategoryId

UNION ALL

SELECT A.nCategoryID, A.strCategoryName, A.nCategoryParentID, A.bLeafCategory, A.strGroupID FROM dbo.tmpCategory A, TopCategory WHERE A.nCategoryID = TopCategory.nCategoryParentID)


SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowNum, * FROM TopCategory
)

go

Select * from dbo.GetChildCategory(3)

Select * from dbo.GetTopCategory(4)

DROP TABLE [dbo].[tmpCategory];