Handy SQL Functions: Number List

Sometimes, in SQL group queries, you need totals over a series(e.g. Month numbers 1 to 12). What do you do if some parts of that range have no data but you still want the number in the series displayed?

For instance in our example you have sales data for Month 1,2,3,5 & 6 but there isn't anything for month number 4. You still want Month 4 displayed as a row, just with a null value.

The easiest way to do this is have a small table with numbers 1 to 12 that you can LEFT JOIN on to.

This function will output a table just like that.

CREATE FUNCTION dbo.fnNumberList
(
 @iStart int,
 @iEnd int,
 @iStep int = 1
)  
RETURNS @RtnValue table 
(
 ircNum int
) 

/*
 Returns a table with column name "ircNum" numbers from iStart to iEnd incrementing/decrementing by iStep
 Example:
 select * from dbo.NumberList(4,10,2);
 
 ircNum
 ------
 4
 6
 8
 10
*/

AS  
BEGIN 
 Declare @Cnt int
 Set @Cnt = @iStart

 if @iStep>0 
 While (@Cnt<=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End

 if @iStep<0 
 While (@Cnt>=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End
 
 Return
END