Thanks Srinivas,

Very good description of recursive common table expressions and great sample for getting hierarchical data with a defined value of drill down levels (sample 1).

I liked the sample 1. I believe it will be very useful for many of us.

Eralper

http://www.kodyaz.com

The sample VB.net code for comparison contains both syntax and logic errors.

In VB.Net, an IF is ended by "End If", not End.

Also, "If result = number * Factorial(number - 1)" is attempted assignment and comparison and produces an incorrect result. If you run this code, you always get zero as a result.

Refactored code:

Private Function Factorial(ByVal number As Integer) As Integer
  If number = 0 Then
    Return 1
  Else
    Return (number * Factorial(number - 1))
  End If
End Function

Thanks for the article.

My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.

I've added : INSERT INTO dbo.SampleOrg SELECT 14, 'Senior Director - Finance', 5

It is not reasnable in this example but it is reasnable in other.

How it should be done to accomplish this need.

Hi,

One of the best example of recursive funtions in SQL. Thanks for posting such a nice example.

Raj