My Thoughts & Views

Sequence Generator Function in SQL

Posted on: May 2, 2008

Here is the sequence generator function written in SQL, which when passed starting and end numbers will generate the rest sequence automatically.

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 RETURNENDGO

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
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
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
RETURNENDGO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: