Recursion

recursion is one of the things that makes it easy to work with data that belongs to different levels. that could e.g. be a organsiation, where finance consist of 3 levels (Finanse –> Purchase –> internatiol) and IT only one level.

these different levels makes it easy to extract data so that you can create an organsiational map, and for that we use Recursion.

I will stick to the organsational example, where the organisation looks like.

what I want is, that I want a list showing me:

1) Organisational unit
2) What organisational unit it belongs to.
3) Level (Company=0 –> Domestic/Internationl =3)

First we need to setup the database. JUST copy the code into your management studio.

—– CODE SNIPPET START —–

— 1. First we do the normal preparations
—    We create the database and jumps right into it.

use MASTER
GO

IF DATABASEPROPERTYEX(‘DemoRecursion’,’Version’)>0
DROP DATABASE DemoRecursion
GO

CREATE DATABASE DemoRecursion
GO

USE DemoRecursion
GO

— 2. Then we create a table

CREATE TABLE OrgUnits    (    ID    INT    IDENTITY PRIMARY KEY,
OrgUnitID            INT UNIQUE,
OrgUnitName            VARCHAR(500),
BelongstoUnitId        INT Null
)
go

—– CODE SNIPPET END —–
Then we need to put in some testdata.

—– CODE SNIPPET START —–

INSERT INTO OrgUnits VALUES(1,’Company’,NULL) — Top of organisation

INSERT INTO OrgUnits VALUES(2,’IT’,1)
INSERT INTO OrgUnits VALUES(3,’Infrastructure’,2)
INSERT INTO OrgUnits VALUES(4,’Software’,2)
INSERT INTO OrgUnits VALUES(5,’Education’,2)

INSERT INTO OrgUnits VALUES(6,’Finance’,1)
INSERT INTO OrgUnits VALUES(7,’Debtors’,6)
INSERT INTO OrgUnits VALUES(8,’Purchase’,6)

INSERT INTO OrgUnits VALUES(9,’Domestic’,8)
INSERT INTO OrgUnits VALUES(10,’International’,8)

—– CODE SNIPPET END —–

NOW WE ARE READY TO DO SOME RECURSION.

To create the desired output, we need to use something called Common Table Expression (or just CTE). that is a way of creating a temporary dataset and then use it in a select clause (the simple description.

Using CTE requires two parts

1) Setting up the temporary dataset
2) Selecting it

So if we forget recursion and just try to use CTE, then try to copy and paste the following code into your SSMS and then run it.

—– CODE SNIPPET START —–

WITH organisation(OrgUnitID, OrgUnitName, BelongsToID, BelongsToName, LEVEL)
AS
(
SELECT OrgUnitID, OrgUnitName,OrgUnitID,OrgUnitName,0
FROM OrgUnits
WHERE BelongstoUnitId IS NULL

)
SELECT *
FROM organisation
—– CODE SNIPPET END —–

I have created a temporary dataset called organsation, and it has 5 fields.

Between () I have created a normalselect statement, and puts all rows into the dataset from table OrgUnits where BelongsToUnitID is null. In our case it means the top of the orgunit.

after the ) I use a select from the dataset. Very simple and it can be joined with all other tables in your database.

A CTE ALWAYS has these two parts

1) WITH xxx AS ()
2) SELECT * FROM xxx

Actually what we have created now is our Anchor in the recursion, so that we now where to start and where to end.

so now we just need to create some code saying:

1) I am at toplevel. Please return all children (IT / FINANCE).
2) After they are return we first move to child number 1 (IT) and says
3) I am at IT please return all childen.
4) If any children then do 2+3 for all children

and basically 2+3+4 are repeated until there are no more children nowhere in the structure.

The code looks like

—– CODE SNIPPET START —–

WITH organisation(OrgUnitID, OrgUnitName, BelongsToID, BelongsToName, LEVEL)
AS
(
— First we create the Anchor
SELECT OrgUnitID, OrgUnitName,OrgUnitID,OrgUnitName,0
FROM OrgUnits
WHERE BelongstoUnitId IS NULL

— Then we creat the recursion part
UNION ALL

SELECT T1.OrgUnitID, T1.OrgUnitName,t2.OrgUnitID,t2.OrgUnitName,Level+1
FROM OrgUnits T1 INNER JOIN organisation T2 ON t1.BelongstoUnitId=t2.OrgUnitID

)
SELECT *
FROM organisation
—– CODE SNIPPET END —–

As you might see, then I have just added UNION ALL and TWO LINES. What you can see is that I create a normal Select Clause and it takes all rows from OrgUnits where the belongsToUnitID are in the temporary dataset.

Let me try to explain

1) First we add the anchor to dataset (it has ID 1)
2) then we select all from Orgunits where BelongstoUnitID is in organisation (meaning is in 1). That returns IT and FINANCE.
3) AHA… Now we have two new rows in the dataset, and we need to select all from OrgUnits where belongstoUnitID that has not been processed (That means 3,4,5+6,7,8).

And we continue with 2+3 until we are gone through all organsational units.

Nice and easy!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s