If you’re using SQL Server’s GENERATE_SERIES() function/relational operator and you’re getting an empty result set, then it could be due to one of the following reasons.

Specifying a Negative Step for an Incrementing Series

One reason you could be getting this issue is that you’re using a negative step for an incrementing series.

Example:

SELECT value FROM GENERATE_SERIES( 1, 3, -1 );

Result

value      
-----------

(0 rows affected)

In this example I specified a start point of 1, an end point of 3, but a step of -1.

This can’t work, because we need a positive value in order to increment from 1 to 3. Using a negative value will decrement the series away from our desired stop point.

So to fix this issue, simply change the step amount to a positive value (or omit it altogether so that the default value of 1 is used):

SELECT value FROM GENERATE_SERIES( 1, 3, 1 );

Result

value      
-----------
          1
          2
          3

Specifying a Positive Step for a Decrementing Series

The second reason you might be getting no results when using the GENERATE_SERIES() function is the opposite of the previous reason; you’re using a positive step value for a decrementing series.

Example:

SELECT value FROM GENERATE_SERIES( 3, 1, 1 );

Result

value      
-----------

(0 rows affected)

In this case, we’re inadvertently trying to increment away from a decrementing series. If the start point is 3 and the stop point is 1, then we need to use a negative step value to get to our end point. But in our example we use a positive step point.

So to fix, simply change the step to a negative value:

SELECT value FROM GENERATE_SERIES( 3, 1, -1 );

Result

value      
-----------
          3
          2
          1

Another way to do it is omit the third argument altogether. When we do this on a decrementing series, the default step value is -1.

Passing a NULL Argument

The third reason you might be getting no results when using the GENERATE_SERIES() function is that at least one of your arguments is NULL.

Example:

SELECT value FROM GENERATE_SERIES( 1, 3, NULL );

Result

value      
-----------

(0 rows affected)

As can be seen here, even providing a NULL step value returns no results.

To fix, be sure that none of your arguments are NULL. This means we can either provide a non-NULL step or omit it altogether if we want it to use the default step.

Here’s an example of omitting the step argument altogether:

SELECT value FROM GENERATE_SERIES( 1, 3 );

Result

value      
-----------
          1
          2
          3

I omitted the third argument and so the default step value of 1 was used. This (positive) value was used because SQL Server could see that we were trying to create an incrementing series (due to the fact that our start point is lower than our end point).

Note that the third argument, if provided, can’t be zero either. Passing zero as the third argument results in the following error:

SELECT value FROM GENERATE_SERIES( 1, 3, 0 );

Result

Msg 4199, Level 16, State 1, Server 4ccb2a47c4bd, Line 1
Argument value 0 is invalid for argument 3 of generate_series function.