CREATE FUNCTION [dbo].[ufn_TallyHo] (@quantity int)
RETURNS TABLE AS
RETURN(
with cte(tally)as(select 1 union all select tally + 1 from cte where tally < @quantity)SELECT tally FROM cte
)
I found a table at work called Tally that was a list of numbers. I thought it was sad until I needed something like it, so I created the above function. But this one is better, no recursion:
ALTER FUNCTION [dbo].[ufn_TallyHo2] (@quantity bigint)
RETURNS TABLE AS
RETURN(
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(N) AS (SELECT 1 FROM CTE5 x, CTE5 y)
SELECT TOP (@quantity) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM CTE6 x, CTE6 y
)