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

Rate

4 (1)

You rated this post out of 5. Change rating