Recursive Stored Procedure query

I stumbled upon a great stored procedure for Recursive Queries (here), but I wanted to recreate it for AdventureWorks.

Note: This one only works on the previous AdventureWorks database (not 2008R2, the Employee table has changed). Soon I’ll be posting a modified version of this based upon the AdventureWorks2008R2 database.

Basicly the Employee-table is self referencing table with levels (also known as ‘Tree Walking‘) has an EmployeeId and ManagerId I’m going to use.

The code:

How to execute:

And ofcourse, the results are shown here:

A really nice thing about this SP is that it uses a parameter Root, which can be used to ‘climb’ the tree. For example, if I set @Root to be 12:

Want the SQL sourcecode? Click here

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s