**byecoliz (2/15/2013)**

Hello,I am calculating the first 20 Fibonacci numbers using a function,but getting wrong answer.The condition is to write a function not a procedure. here is my try,help me.

create function fn_fibo(@end int)

returns int

as

begin

declare @a int, @b int,@fib int

set @a = 0

set @b = 1

set @fib = 0

while @fib < @end

begin

set @fib = @a + @b

set @a = @b

set @b = @fib

end

return @fib

end

select dbo.fn_fibo(20)What I'm really looking at from above is the following...

The condition is to write a function not a procedure.

That means one of two things.

1. This is for some type of class you need to pass.

2. This is for your job (I think not likely here but whatever).

If we're going to do this, let's knock the problem right out of the park.

First, one of the most important things to do is to do a little research.

1. We find that the largest positive number that the INT data-type can be is 2,147,483,647 and that a BIGINT can handle 9,223,372,036,854,775,807.

2. Doing a bit of work in a spreadsheet using conventional calculation methods, we find that the largest Fib Number we can fit into an INT is the 46th Fib number or 1,836,311,903. So, instead, we decide to return a BIGINT from the function.

3. Doing a bit of Googling, we figure out how to use an exact calculation using the "Golden Ratio" and "Binet's Formula" to directly calculate Fibonacci numbers. We also come to the understanding that we can't directly calculate more than the 70th Fib number using this method because the FLOAT data-type returned by the SQRT function induces rounding errors after the precision of the return reaches just 15 digits. We could use a "pseudo cursor" to get up to the 92nd Fib number (BIGINT fails after that) but we'll trade a little of the domain for blinding speed. Besides, if you need more than the 70th Fib number, then you're probably using the wrong tool. ;-)

4. We also find that traditional methods of error checking don't work in a function but we still want to give more information about bad inputs. For sure, we don't want anyone to try to calculate more than the 70th Fib number because it will be an imprecise number.

5. We also want this to be as fast as possible so it's absolutely essential to avoid all loops and other forms of RBAR including recursive CTEs that count.

With all of that in mind, here's a function the will return the Nth Fibonacci number so long as N is between 1 and 70. It will return a "TOP" error for numbers less than 0, nothing for 0, a table of Fib numbers from 1 to whatever @End is providing 1 <= @End <= 70, and a thoughful error where @End > 70.

CREATE FUNCTION dbo.FibNumber

(@End INT)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH

R3(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

R81(N) AS (SELECT 1 FROM R3 a, R3 b, R3 c, R3 d),

cteTally(N) AS (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R81)

SELECT t.N,

FibNumber = CAST((1/SQRT(5))*(POWER((1+SQRT(5))/2,t.N)-POWER((1-SQRT(5))/2,t.N)) AS BIGINT)

FROM cteTally t

WHERE 1 = CASE

WHEN @End BETWEEN 1 AND 70

THEN 1

ELSE 1/'[Error: Parameter of FibNumber greater than 70]'

END

;

You use it to return your 20 Fib numbers like this...

SELECT * FROM dbo.FibNumber(20);

For more information on how the cCTEs (Cascading CTEs) work to create numbers and how a Tally table or cteTally can replace certain loops, please see the following articles.

http://www.sqlservercentral.com/articles/T-SQL/62867/http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers-103056 As a sidebar, you might want to get out of the habit of using "Hungarian Notation" (the "fn_" prefix on your function). It's a bit of a yester-year habit that has seriously gone out of favor with many DBAs.

Last but not least, avoid the loop wherever and whenever you can.

--Jeff Moden

**RBAR** is pronounced ree-bar and is a Modenism for

**R**ow-

**B**y-

**A**gonizing-

**R**ow.

First step towards the paradigm shift of writing Set Based code:

**Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.**

If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

**Helpful Links:**

**How to post code problems****How to post performance problems****Forum FAQs**