Number Table Function with Step

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Number Table Function with Step

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 13:54:42

This is a slightly enhanced version of the Number Table Function that allows the number sequence to increment in steps. It uses the F_TABLE_NUMBER_RANGE function on the link below, so both are required.

Original Number Table Function, F_TABLE_NUMBER_RANGE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


if objectproperty(object_id('dbo.F_TABLE_NUMBER_RANGE_STEP'),'IsInlineFunction') = 1
	begin drop function dbo.F_TABLE_NUMBER_RANGE_STEP end
go
create function dbo.F_TABLE_NUMBER_RANGE_STEP
(
	@START_NUMBER		int,
	@END_NUMBER		int,
	@STEP			int
)
returns table 
as
return(
select
	NUMBER = a.NUMBER*isnull(@STEP,1)
from
	dbo.F_TABLE_NUMBER_RANGE(@START_NUMBER,@END_NUMBER) a
)
go
select * from dbo.F_TABLE_NUMBER_RANGE_STEP(1,50,20)

Results:


NUMBER      
----------- 
20
40
60
80
100
120
140
160
180
200
220
...
...
900
920
940
960
980
1000

(50 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources