SQL Server - recursion

[Naoise]

I'd like to use a recursive function to calculate the depth of each Role as it is added. So a new field called depth will be added to the table tblOrganisationRoleOrgChart.

Given these two tables

tblOrganisationRoles
===========================
OrganisationRoleID int | OrganisationID int | Role varchar
1 1000 CEO
2 1000 CTO
3 1000 Tech
4 1000 General

tblOrganisationRoleOrgChart
===========================
OrganisationRoleID int | ReportsToID int | Depth
1 1
2 1
3 2
4 2

How would you write a recursive function to calculate the depth of a given Role as it was added? Any help appreciated

 

[AlexCuse]

This depends greatly on what version of SQL Server you use.

If you have sql server 2005 I suggest you do a search for [google]Recursive Common Table Expression SQL Server 2005[/google] as this could help you.

In SQL 2000, I think you will need to use a scalar UDF. Something like this may work (not tested in the least):

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] DepthCheck (@ORepID [COLOR=blue]int[/color])
returns [COLOR=blue]int[/color]
[COLOR=blue]as[/color]

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @b [COLOR=blue]bit[/color]
[COLOR=blue]set[/color] @b = 0

[COLOR=blue]declare[/color] @depth [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @depth = 0

[COLOR=blue]while[/color] @b <> 1
[COLOR=blue]begin[/color]

[COLOR=blue]if[/color] exists([COLOR=blue]select[/color] * [COLOR=blue]from[/color] tblOrganisationRoleOrgChart [COLOR=blue]where[/color] OrganisationRoleID = @ORepID
	and OrganisationRoleID <> ReportsToID)
[COLOR=blue]begin[/color]	
	[COLOR=blue]set[/color] @depth = @depth + 1
	[COLOR=blue]select[/color] @ORepID = ReportsToID [COLOR=blue]from[/color] tblOrganisationRoleOrgChart [COLOR=blue]where[/color] OrganizationRoleID = @ORepID
[COLOR=blue]end[/color]
[COLOR=blue]else[/color]
[COLOR=blue]begin[/color]
	[COLOR=blue]set[/color] @b = 1
[COLOR=blue]end[/color]

[COLOR=blue]end[/color]

[COLOR=blue]return[/color] @depth

[COLOR=blue]end[/color]

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Add a depth column to your table and keep it updated. When you insert new rows, you only have to join to the parent to learn its depth and add 1.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]