LISTING 4: UDFs for Sequence Generation

CREATE FUNCTION fn_p
(
	@exp  int
)
RETURNS @result TABLE (id int)
AS
BEGIN
	DECLARE @pow int
	SET @pow = POWER(10, @exp)

	INSERT @result
		SELECT 0 id UNION ALL 
		SELECT 1 * @pow UNION ALL
		SELECT 2 * @pow UNION ALL
		SELECT 3 * @pow UNION ALL
		SELECT 4 * @pow UNION ALL
		SELECT 5 * @pow UNION ALL
		SELECT 6 * @pow UNION ALL
		SELECT 7 * @pow UNION ALL
		SELECT 8 * @pow UNION ALL
		SELECT 9 * @pow
	RETURN
END
GO

CREATE FUNCTION fn_sequence
(	
	@start int,
	@end  int
)
RETURNS @result TABLE(id int)
AS
BEGIN
	IF @start > @end RETURN
	
	IF @end < 10
		INSERT @result
		SELECT id FROM
		(
			SELECT
				a0.id id
			FROM
				fn_p(0) a0
		) d
		WHERE
			id BETWEEN @start AND @end
	ELSE

	IF @end < 100
		INSERT @result
		SELECT id FROM
		(
			SELECT
				(a1.id + a0.id) id
			FROM
				fn_p(1) a1 CROSS JOIN
				fn_p(0) a0
		) d
		WHERE id BETWEEN @start AND @end
	ELSE

BEGIN CALLOUT A
	IF @end < 1000
		INSERT @result
		SELECT id FROM
		(	
			SELECT
				(a2.id + a1.id + a0.id) id
			FROM
				fn_p(2) a2 CROSS JOIN
				fn_p(1) a1 CROSS JOIN
				fn_p(0) a0
		) d
		WHERE id BETWEEN @start AND @end		
	ELSE
END CALLOUT A

	IF @end < 10000
		INSERT @result
		SELECT id FROM
		(
			SELECT
				(a3.id + a2.id + a1.id + a0.id) id
			FROM
				fn_p(3) a3 CROSS JOIN
				fn_p(2) a2 CROSS JOIN
				fn_p(1) a1 CROSS JOIN
				fn_p(0) a0
		) d
		WHERE id BETWEEN @start AND @end		

	RETURN
END
GO