Wednesday, January 27, 2010

Common Table Expression Example

Hello Friends,
Here I put the sample code example using CTE.

--Generate Fibonacii Series using CTE


;WITH FibonaciiSeries(N,F1,F2,F3)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT),CAST(0 AS BIGINT),CAST(1+0 AS BIGINT)
UNION ALL
SELECT N+1, F2,F3,F2+F3 FROM FibonaciiSeries WHERE N<92
)
SELECT * FROM FibonaciiSeries

--OUTPUT
-------------------------



--Find Factorial

;WITH Factorial(N,Number)
AS
(
SELECT CAST (1 AS BIGINT), CAST (1 AS BIGINT)
UNION ALL
SELECT N+1,CAST ((N+1)*(Number) AS BIGINT) FROM Factorial WHERE N<10
)

SELECT * FROM Factorial

--OUTPUT
-------------------------

No comments: