CODEWITHSUNDEEP
sql-serversql-server-2008
Adam
Adam

Reputation: 2455

Recursive Function in MS SQL

I have following two tables:

SELECT [AM_ID]
  ,[AMI_ID]
  ,[Parent_AMI_ID]
  ,[AMI_Code]
FROM [Reporting].[dbo].[DIM_AM]
;

This table contains hierarchy (37 rows)

SELECT [AMI_ID]
  ,[AMI_Name]
FROM [Reporting].[dbo].[DIM_AMI]
;

This table contains entities (AMI_Name).

And i have to make a table using recursive function in MS SQL that will return following:

SELECT
Level
,AMI_Code
,AMI_Name
FROM...

How can I use the recursive function to generate hierarhy in this format? How do you write recursive query with recursive function in MS SQL in General? Give me some Suggestion please.

Upvotes: 0

Views: 1821

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is a little example:

DECLARE @DIM_AM TABLE([AM_ID] INT, [AMI_ID] int, [Parent_AMI_ID] INT, [AMI_Code] VARCHAR(20))
DECLARE @DIM_AMI TABLE([AMI_ID] INT, [AMI_Name] VARCHAR(20))

INSERT INTO @DIM_AMI VALUES
(1, 'AMI1'),
(2, 'AMI2'),
(3, 'AMI3'),
(4, 'AMI4')

INSERT INTO @DIM_AM VALUES
(1, 1, NULL, 'CODE_AMI1'),
(2, 2, 1, 'CODE_AMI2'),
(3, 3, 1, 'CODE_AMI3'),
(4, 4, 3, 'CODE_AMI4')



;WITH cte AS(SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL
             UNION ALL
             SELECT d.*, c.Level + 1 FROM cte c 
             JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID)
SELECT c.Level, c.AMI_Code, d.AMI_Name FROM cte c
JOIN @DIM_AMI d ON d.AMI_ID = c.AMI_ID

Output:

Level   AMI_Code    AMI_Name
0       CODE_AMI1   AMI1
1       CODE_AMI2   AMI2
1       CODE_AMI3   AMI3
2       CODE_AMI4   AMI4

This is recursive common table expression(cte):

;WITH cte AS(SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL
             UNION ALL
             SELECT d.*, c.Level + 1 FROM cte c 
             JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID)

First part is starting point where you select top level elements(WHERE Parent_AMI_ID IS NULL):

SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL

Then by syntax you need union all.

Then comes recursive part that selects children of previous select:

SELECT d.*, c.Level + 1 FROM cte c 
JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID

Upvotes: 1

Related Questions