Using Common Table Expression

Many projects store the hierarchical data into database. This hierarchical data is used to generate the tree view on UI like folder structure in the explorer, location hierarchy and company hierarchy in account software.

Generally data is stored in the database in a one table. Let’s take an example so that we can understand better. Let’s say we need to create the location hierarchy in form of tree view on screen. It will look like this

Asia (1)
- India (3)
- Mumbai (7)
- Delhi (8)
- Calcutta (9)
- Bangalore (10)
- Japan (4)
- Tokyo (11)
- Nagasaki (12)
- Hiroshima (13)
Europe (2)
- England (5)
- London (14)
- Manchester (15)
- France (6)
- Paris (16)

Here we assumed that data in brackets are their Locationid. So our database table will look like below and u can also create in u r sample database.

CREATE TABLE Locations
(
LocationID int primary key not null,
ParentLocationID int null,
LocationName varchar(50) not null
)

Insert the values …..

INSERT Into Locations Values (1, null, 'Asia')
INSERT Into Locations Values (2, null, 'Europe')
INSERT Into Locations Values (3, 1, 'India')
INSERT Into Locations Values (4, 1, 'Japan')
INSERT Into Locations Values (5, 2, 'England')
INSERT Into Locations Values (6, 2, 'France')
INSERT Into Locations Values (7, 3, 'Mumbai')
INSERT Into Locations Values (8, 3, 'Delhi')
INSERT Into Locations Values (9, 3, 'Calcutta')
INSERT Into Locations Values (10, 3, 'Bangalore')
INSERT Into Locations Values (11, 4, 'Tokyo')
INSERT Into Locations Values (12, 4, 'Nagasaki')
INSERT Into Locations Values (13, 4, 'Hiroshima')
INSERT Into Locations Values (14, 5, 'London')
INSERT Into Locations Values (15, 5, 'Manchester')
INSERT Into Locations Values (16, 6, 'Paris')

To get the above mentioned o/p we can use Recursive Stored Procedure. But if u write recursive stored procedure it supports only 32 levels. And u might land up with the following error….

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hence there is another way to get rid of this explained below…

CREATE PROCEDURE GetLocationTree_OneQuery
@LocationID int = 1
AS
BEGIN
WITH LocationTree(LocationID, ParentLocationID, LocationName, Levels)
AS
(
-- anchor member
SELECT LocationID, ParentLocationID, LocationName, 1
FROM Locations
WHERE LocationId = @LocationID

UNION ALL

-- recursive member
SELECT
L.LocationID, L.ParentLocationID, L.LocationName, LocationTree.Levels + 1
FROM Locations L
INNER JOIN LocationTree
ON LocationTree.LocationID = L.ParentLocationID
)

SELECT * From LocationTree
END

If look at carefully this stored procdedure has a 2 queries one with createting a temporay table for use and fireing recursive trigger internally and the last second one just gives the o/p table.

“With” statement creates the temp table “LocationTree” for use with the columns that we can define. Using this table we can fire the recursive query with the original table.
It has also limited of recursion up to 100 levels………. But u in real world u might not required too many levels




Recursive Stored Procedure

CREATE PROCEDURE GetLocations
@LocationID int = 1
AS
BEGIN
Create Table #NestedTree(
LocationID int,
ParentLocationID int,
LocationName varchar(50),
LocationLevel int
)

INSERT into #NestedTree SELECT LocationID, ParentLocationID,
LocationName, 1
FROM Locations where LocationID = @LocationID

EXEC GetLocationTree @LocationID, 1

SELECT
LocationID,
ParentLocationID,
LocationName,
LocationLevel
FROM #NestedTree

DROP TABLE #NestedTree

END

GO

CREATE PROCEDURE GetLocationTree
@LocationID int = 1,
@Level int = 0
AS
BEGIN

SET NOCOUNT ON
DECLARE @NextLevel int, @PLocationID int, @LocationName VARCHAR(50)

-- Build a cursor to loop through all the kids of this post
DECLARE c1 CURSOR LOCAL
FOR SELECT LocationID, LocationName FROM Locations
WHERE ParentLocationID = @LocationID

SET @PLocationID = @LocationID

SET @Level = @Level + 1

OPEN c1

FETCH NEXT FROM c1 INTO @LocationID, @LocationName

WHILE @@FETCH_STATUS = 0

BEGIN
-- Put this record in the temp table
INSERT #NestedTree (LocationID,
ParentLocationID,
LocationName,
LocationLevel)
VALUES (@LocationID, @PLocationID, @LocationName, @Level)

-- Process all the children for this post
EXEC GetLocationTree @LocationID, @Level

-- And get the next record at this level
FETCH NEXT FROM c1 INTO @LocationID, @LocationName
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF
END

/////// ************************************* ///////

Run the Stored Procedure query in the Query Analyzer

GetLocations 1
This will give you Asia and the location below that with the levels also if the levels are here limited to three but if you think of unlimited levels this recursive procedure will fail since it only supports up to 32 levels and u might land up with following error.

Msg 217, Level 16, State 1, Procedure GetLocationTree
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


That means u cannot call stored procedure under the stored procedure after 32 levels. So what is other solution to get rid of this? Definitely Microsoft has answer to this also.