SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fibonacci numbers using a function.


Fibonacci numbers using a function.

Author
Message
byecoliz
byecoliz
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 24
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61873 Visits: 17954
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)


It looks like your calculation is correct but your logic isn't quite. You need to look at what you are doing here. You want a collection of numbers but your function returns an int. You need to do something inside your loop instead of just adding the numbers. There is no chance you can make a scalar function return the first 20 numbers. To do this you will need to use a table valued function.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
David Webb-CDS
David Webb-CDS
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4022 Visits: 8586
As Sean said, you can't return 20 numbers in an int. You could string them together in a single return value like so:

create function fn_fibo(@end int)
returns varchar(255)
as
begin
declare @a int, @b int,@fib int, @counter int
declare @fstring varchar(255)
set @end = @end - 2
set @a = 0
set @b = 1
set @fib = 0
set @counter = 0
select @fstring = CAST(@a as varchar(10)) + ','
select @fstring = @fstring + CAST(@b as varchar(10))
while @counter < @end
begin
select @fstring = @fstring + ','
set @fib = @a + @b
set @a = @b
set @b = @fib
select @fstring = @fstring + CAST(@fib as varchar(20))
set @counter = @counter + 1
end



That's the long way around and I'm sure there are other folks who can jump in with more efficient solutions, but that's the basic idea.



And then again, I might be wrong ...
David Webb
byecoliz
byecoliz
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 24
Wow, thanks David for that solution, and Sean thanks too for the guidance.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61873 Visits: 17954
Here is David's solution with the syntax errors corrected.


create FUNCTION fn_fibo (@end INT)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @a INT
,@b INT
,@fib INT
,@counter INT
DECLARE @fstring VARCHAR(255)

SET @end = @end - 2
SET @a = 0
SET @b = 1
SET @fib = 0
SET @counter = 0

SELECT @fstring = CAST(@a AS VARCHAR(10)) + ','

SELECT @fstring = @fstring + CAST(@b AS VARCHAR(10))

WHILE @counter < @end
BEGIN
SELECT @fstring = @fstring + ','

SET @fib = @a + @b
SET @a = @b
SET @b = @fib

SELECT @fstring = @fstring + CAST(@fib AS VARCHAR(20))

SET @counter = @counter + 1
END

RETURN @fstring
END



This works but it returns all the values in a big long string that you now have to break apart to make it usable. It also is a scalar function which is notoriously slow. Add to this the while loop and this could get nasty quickly.

I converted this into an inline table value function like this.


create FUNCTION itvf_fibo (@end INT)
RETURNS @FibNums table
(
FibNum int
)
AS
BEGIN
DECLARE @a INT
,@b INT
,@fib INT
,@counter INT

SET @end = @end - 2
SET @a = 0
SET @b = 1
SET @fib = 0
SET @counter = 0

insert @FibNums
select @a union all
select @b

WHILE @counter < @end
BEGIN
SET @fib = @a + @b
SET @a = @b
SET @b = @fib
insert @FibNums
select @fib
SET @counter = @counter + 1
END
return;
END



I would like to take this one step further and turn this into a set based approach using a tally table. Unfortunately I have a meeting in about 2-3 minutes. If nobody else happens along I will try to pick this up.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212531 Visits: 41977
I don't understand why people write functions for this. No matter how many time you run the code , the values will NEVER change.

Why not just build a "helper" table to hold the values?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61873 Visits: 17954
Jeff Moden (2/15/2013)
I don't understand why people write functions for this. No matter how many time you run the code , the values will NEVER change.

Why not just build a "helper" table to hold the values?


Agreed 100%. My guess is this has been a learning experience so from that perspective it has been worth it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212531 Visits: 41977
Sean Lange (2/15/2013)
I converted this into an inline table value function like this.



Careful now. That's not an "inline" TVF. That's a "multiline" TVF. An iTVF must be a single query much like you'd write a view.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212531 Visits: 41977
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 Row-By-Agonizing-Row.
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
byecoliz
byecoliz
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 24
Hi Jeff thank you for the clarification,i understand well what you mean.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search