;WITH fib AS (
	SELECT
		CAST(1 AS DECIMAL(38)) AS [value]
		, CAST(0 AS DECIMAL(38)) AS [prec]
		, CAST(1 AS INT) AS [Level]

	UNION ALL SELECT
		CAST(f.[value] + f.[prec] AS DECIMAL(38)) AS [value]
		, f.[value] AS [prec]
		, f.[level] + 1 AS [level]
	FROM
		fib f 
	WHERE
		[level] < 183
	)
SELECT * FROM fib
OPTION (MAXRECURSION 0)

;WITH fib AS (
	SELECT
		CAST(1 AS FLOAT) AS [value]
		, CAST(0 AS FLOAT) AS [prec]
		, CAST(1 AS INT) AS [Level]

	UNION ALL SELECT
		CAST(f.[value] + f.[prec] AS FLOAT) AS [value]
		, f.[value] AS [prec]
		, f.[level] + 1 AS [level]
	FROM
		fib f 
	WHERE
		[level] < 1476
	)
SELECT * FROM fib
OPTION (MAXRECURSION 0)

-- VARCHAR(MAX)
DECLARE @before VARCHAR(MAX)
DECLARE @current VARCHAR(MAX)
DECLARE @level INT
DECLARE @iterations INT

DECLARE @curCounter INT
DECLARE @befCounter INT

DECLARE @barrel VARCHAR(31)
DECLARE @next VARCHAR(MAX)
SET NOCOUNT ON

DECLARE @overflow INT
DECLARE @looper INT

DECLARE @fib TABLE (
	[level] INT
	, [value] VARCHAR(MAX)
	)

SET @before = '0'
SET @current = '1'
SET @level = 1
SET @iterations = 3000

INSERT INTO @fib SELECT @level, @current

WHILE ( @level < @iterations ) BEGIN

	SET @curCounter = LEN(@current)
	SET @befCounter = LEN(@before)

	SET @next = ''
	SET @overFlow = 0
	SET @looper = 1

	WHILE ( @curCounter > 0 ) BEGIN

		SET @barrel = 
			RIGHT(
				'00000000000000000000000000000000'
				+ CAST(
					CAST(
						SUBSTRING(
							@current
							, CASE WHEN @curCounter > 30 THEN @curCounter -29 ELSE 1 END
							, CASE WHEN @curCounter > 30 THEN 30 ELSE @curCounter END
						)
						AS NUMERIC(31)
					)

					+ CASE
						WHEN @befCounter < 1 THEN 0
						ELSE CAST(
							SUBSTRING(
								@before
								, CASE WHEN @befCounter > 30 THEN @befCounter -29 ELSE 1 END
								, CASE WHEN @befCounter > 30 THEN 30 ELSE @befCounter END
							)
							AS NUMERIC(31)
						)
					END

					+ CAST(@overFlow AS NUMERIC(31))
				
				AS VARCHAR(31))
			, 31)

		SET @overFlow = CAST(LEFT(@barrel, 1) AS INT)

		SET @curCounter = @curCounter - 30
		SET @befCounter = @befCounter - 30

		SET @next = @next + REVERSE(RIGHT(@barrel,30))
	END

	SET @next = @next + CAST(@overFlow AS CHAR(1))
	SET @next = REVERSE(@next)
	SET @next = SUBSTRING(@next, PATINDEX('%[^0]%', @next), LEN(@next) -  PATINDEX('%[^0]%', @next) + 1)


	SET @before = @current
	SET @current = @next
	SET @level = @level + 1

	INSERT INTO @fib
	SELECT @level, @current
END
		

-- Check the first 183 numbers to see if they are correct.
;WITH fib AS (
	SELECT
		CAST(1 AS DECIMAL(38)) AS [value]
		, CAST(0 AS DECIMAL(38)) AS [prec]
		, CAST(1 AS INT) AS [Level]

	UNION ALL SELECT
		CAST(f.[value] + f.[prec] AS DECIMAL(38)) AS [value]
		, f.[value] AS [prec]
		, f.[level] + 1 AS [level]
	FROM
		fib f 
	WHERE
		[level] < 183
	)
SELECT
	wh.[level] AS [While Step]
	, wh.[value] AS [While Value]
	, cte.[value] AS [CTE Value]
	, LEN(wh.[value]) AS [Decimal Places]
	
FROM
	@fib wh
	LEFT JOIN fib cte ON cte.[level] = wh.[level]

 OPTION (MAXRECURSION 0)

CREATE PROCEDURE dbo.uspFibonacci
(
	@Fibs SMALLINT = 100
)
AS

SET NOCOUNT ON

SET	@Fibs = COALESCE(ABS(@Fibs), 100)

CREATE TABLE	#Fib
		(
			n INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			c INT NOT NULL,
			f VARCHAR(MAX) NOT NULL
		)

IF @Fibs >= 1
	INSERT	#Fib
	SELECT	1,
		'0'

IF @Fibs >= 2
	INSERT	#Fib
	SELECT	1,
		'1'

DECLARE	@f1 VARCHAR(MAX),
	@f2 VARCHAR(MAX),
	@f VARCHAR(MAX),
	@Index INT,
	@e TINYINT,
	@m TINYINT

SELECT	@f1 = '1',
	@f2 = '0'

WHILE @Fibs >= 3
	BEGIN
		IF LEN(@f1) < LEN(@f2)
			SET @f1 = REPLICATE('0', LEN(@f2) - LEN(@f1)) + @f1
		ELSE IF LEN(@f2) < LEN(@f1)
			SET @f2 = REPLICATE('0', LEN(@f1) - LEN(@f2)) + @f2

		SELECT	@f = '',
			@e = 0,
			@Index = LEN(@f1)

		WHILE @Index >= 1
			SELECT	@m = (@e + SUBSTRING(@f1, @Index, 1) + SUBSTRING(@f2, @Index, 1)) % 10,
				@e = (@e + SUBSTRING(@f1, @Index, 1) + SUBSTRING(@f2, @Index, 1)) / 10,
				@f = CHAR(@m + 48) + @f,
				@Index = @Index - 1

		IF @e > 0
			SET	@f = CHAR(@e + 48) + @f

		INSERT	#Fib
		VALUES	(
				DATALENGTH(@f),
				@f
			)

		SELECT	@f2 = @f1,
			@f1 = @f,
			@Fibs = @Fibs - 1
	END

SELECT		n,
		c,
		f
FROM		#Fib
ORDER BY	n

2397334346100631452333336800023778743396400988090212332865227234032387117767626167465060795065595580850691237390963845987165478074085124644348902530685083246709423858342692329718110162972268152200857232686119638781547238020078362945470777668711057069618425746387920931255084621360135655698456629322111614827324455767748623844363426260372374195153577101298837831208580530677289982029527164306876024342838547454228388796380077029917639469963653048076473269452943584037848773158456736367057460079075603072996653089318046279296240100777360367200040226807430924334616931577257195085793060133817911514540227011756335999604550121968663793604830945238116686325506344893928776515696088851468818023735825546502317562957459506612704850760351077006532507519813600498603205937022956740021970327599548184626715032015801445754074519753924901317605013561516613650173445818028242577356369143977719495739428130191089993769093308407443558168431535751910046557480949313497996285124526992631353143367314930548703966553707195171094152730704138121243470432644848607501

quote:

---

Originally posted by Lamprey

I didn't spend too much time looking over your code, but it looks like you are chunking the string into numbers of a length at most 30 digists long and then doing some math and converting back to string. I don't see why this wouldn't work up to the VARCAHR 8000 maximum or even past that using VARCHAR(MAX). I'd have to play around with it.

I did find this article, it uses a function that "adds" the numbers a digit at a time, so I think your way would be better:[url]http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-server-fibonacci-sequence[/url] Notice a mroe compact version of the function in the comments.

---

fibs = 0 : scanl (+) 1 fibs
fib n = fibs !! (n-1) -- Haskell's list subscript operator is zero-baed