Tally Generator

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