A finbonacci sequence is a sequence of numbers that add the two previous numbers to determine the next number in the sequence.
So a sequence starting with 0 and 1 would look like…
0,1...........0 + 1
0,1,1.........1 + 1
0,1,1,2.......1 + 2
0,1,1,2,3.....2 + 3
0,1,1,2,3,5...3 + 5
etc.
I am using a sequence table to determine how many times I want to calculate the next number. The sequence table consists of one column, Number. The rows in this table start at zero, and continue through 10,000. So, 0,1,2,3,4,5…
My procedure looks as follows:
IF OBJECT_ID('prTestDemo') IS NOT NULL
BEGIN
DROP PROCEDURE prTestDemo
END
GO
/*
TEST
DECLARE @ExpectedResult VARCHAR(128) = '0,1,1,2,3,5,8,13,21,34,55,89,144,233'
DECLARE @Result as VARCHAR(128)
EXEC prTestDemo 0, 13, @Result OUT
PRINT @Result
IF @Result != @ExpectedResult
RAISERROR ('Result %s <> %s', 12, 12, @ExpectedResult, @Result)
SET @ExpectedResult = '0,1,1,2,3,5,8,13,21,34,55,89,144,FAIL'
EXEC prTestDemo 0, 12, @Result OUT
PRINT @Result
IF @Result != @ExpectedResult
RAISERROR ('Result %s <> %s', 12, 12, @ExpectedResult, @Result)
*/
CREATE PROCEDURE prTestDemo
(
@Start int
,@Iterations INT
,@Result VARCHAR(256) OUT
)
AS
SET NOCOUNT ON
DECLARE @Iteration INT = 0
DECLARE @Current INT = @Start
DECLARE @Next INT = CASE WHEN @Start = 0 THEN 1 ELSE 0 END
DECLARE @Transfer INT
DECLARE @Fibonacci VARCHAR(256) = CONVERT(VARCHAR(10), @Start)
SELECT
@Fibonacci += ',' + CONVERT(VARCHAR(10), @Next)
,@Transfer = @Next
,@Next = @Current + @Transfer
,@Current = @Transfer
FROM Sequence
WHERE Number < @Iterations
SET @Result = @Fibonacci
GO
To execute the test scipt embedded in the comments, select the test code with your mouse, and then press Ctrl->E to run the selected code. The following results should appear:
0,1,1,2,3,5,8,13,21,34,55,89,144,233
0,1,1,2,3,5,8,13,21,34,55,89,144
Msg 50000, Level 12, State 12, Line 12
Result 0,1,1,2,3,5,8,13,21,34,55,89,144,FAIL <> 0,1,1,2,3,5,8,13,21,34,55,89,144
As you can see, I run the procedure twice. The first time I set my expected result variable to what the procedure should return. The second time I set the expected result to an incorrect value in order to test that my RAISERROR statement works correctly.
If you want to see the procedure not working correctly, rather than my contrived failed test, try changing the order of the temp variable exchanges in the Select clause. For example:
SELECT
@Fibonacci += ',' + CONVERT(VARCHAR(10), @Next)
,@Transfer = @Next
,@Current = @Transfer
,@Next = @Current + @Transfer
FROM Sequence
WHERE Number < @Iterations
Cheers,
Ben
Spoiler Alert....Answer Follows!
Making the above change results in a Binary Sequence...
0,1,2,4,8,16,32,64,128,256,512,1024,2048,4096
Msg 50000, Level 12, State 12, Line 6
Result 0,1,1,2,3,5,8,13,21,34,55,89,144,233 <> 0,1,2,4,8,16,32,64,128,256,512,1024,2048,4096