Recursive Function In Sql Server 2005?

Can anybody suggest programming examples that illustrate recursive functions? For example fibonacci series or factorial..

Solution 1:

Search for "common table expressions." See also this link

Update Adding example from the above-referenced link:

;WITH Fibonacci(n, f, f1)
AS (
        -- This is the anchor part-- Initialize level to 1 and set the first two values as per definitionSELECTCAST(1ASBIGINT),
                CAST(0ASBIGINT),
                CAST(1ASBIGINT)

        UNIONALL-- This is the recursive part-- Calculate the next Fibonacci value using the previous two values-- Shift column (place) for the sum in order to accomodate the previous-- value too because next iteration need them bothSELECT  n +1,
                f + f1,
                f
        FROM    Fibonacci
        -- Stop at iteration 93 because we than have reached maximum limit-- for BIGINT in Microsoft SQL ServerWHERE   n <93
)
-- Now the easy presentation partSELECT  n,
        f AS Number
FROM    Fibonacci

Solution 2:

Here are a few articles that I found using google.com ;)

Recursion in T–SQLUsing recursion in stored proceduresA Recursive User-Defined Function (SQL Server 2000)

Solution 3:

For CTE query recursion see this link. http://www.4guysfromrolla.com/webtech/071906-1.shtml

For TSQL procedure/function recursion see this link http://msdn.microsoft.com/en-us/library/aa175801%28SQL.80%29.aspx