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-2 = 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 @b-2 = @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-2 = 1
SET @fib = 0
SET @counter = 0
insert @FibNums
select @a union all
select @b-2
WHILE @counter < @end
BEGIN
SET @b-2 = @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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/