Technical Article

Tally Generator

,

The function is based on well known Tally function published on SSC.

I added parameters to make it more flexible and easier to use in complicated queries.

Use cases:

Sequential numbers from 1 to 100, step 1:

select N 

from dbo.TallyGenerator (1,100, null, 1)
100 sequential numbers starting from 15 , step 1:
select N 
from dbo.TallyGenerator (15,null, 100, 1)
Counting down from 100 to 1, step 11:
select N 
from dbo.TallyGenerator (100,1, null, -11)
Top 100 numbers less or equal 256:
select N 
from dbo.TallyGenerator (256, null, 100,-1)

IF OBJECT_ID (N'dbo.TallyGenerator') IS NOT NULL
    DROP FUNCTION dbo.TallyGenerator
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TallyGenerator] (
 @StartValue bigint=-32768, 
     @EndValue bigint= 32767, 
 @Rows INT = 1000000 , -- number of rows to be returned. Used only when either @StartValue or @EndValue is not supplied
     @Increment smallint=1
)
RETURNS TABLE
AS RETURN
(
with BaseNum (N) as (
 select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all
 select 1 union all select 1 union all select 1 union all select 1 union all select 1
 ),
 L1 (N) as (
 select bn1.N
 from BaseNum bn1
 cross join BaseNum bn2),
 L2 (N) as (
 select top ( ISNULL(abs( @EndValue - @StartValue) /abs(@Increment)+ 1, @Rows))
 a1.N
 from L1 a1
 cross join L1 a2),
 L3 (N) as (
 select top (ISNULL(abs( @EndValue - @StartValue) /abs(@Increment)+ 1, @Rows) )
 a1.N 
 FROM L2 a1 
 cross join L2 a2 
 cross join L2 a3 
 ),
 Tally (N, Increment) as (
 SELECT row_number() over (order by a1.N), ISNULL(SIGN(@EndValue - @StartValue), SIGN(@Increment)) * ABS (@Increment)
 FROM L3 a1
 )
 SELECT ((N - 1) * Increment) + ISNULL(@StartValue, @EndValue - @Rows*@Increment + 1)  as N 
 FROM Tally
)

GO

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating