If you’re getting SQL Server error 4199 that reads “Argument value 0 is invalid for argument 3 of generate_series function“, it’s probably because you’re passing zero as the third argument to the GENERATE_SERIES() function.

The GENERATE_SERIES() function accepts an optional third argument, but this argument can’t be zero.

To fix this error, either pass a non-zero expression as the third argument, or omit the argument altogether (in order to use the default step of 1).

Example of Error

Here’s an example of code that produces the error:

SELECT * FROM GENERATE_SERIES( 1, 10, 0 );

Result:

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

The error occurred because I passed 0 as the third argument. As mentioned, if we provide a third argument, it must be a non-zero value.

Solution 1

One way to solve this problem is to use a non-zero value as the third argument:

SELECT * FROM GENERATE_SERIES( 1, 10, 2 );

Result:

value      
-----------
          1
          3
          5
          7
          9

The third argument can be a negative value if required:

SELECT * FROM GENERATE_SERIES( 10, 1, -2 );

Result:

value      
-----------
         10
          8
          6
          4
          2

But when we do this, the first argument must be greater than the second (otherwise nothing will be returned).

The third argument, if provided, must be a non-zero expression of type tinyint, smallint, int, bigint, decimal, or numeric.

Solution 2

Another way to get rid of the error is to remove the third argument altogether:

SELECT * FROM GENERATE_SERIES( 1, 10 );

Result:

value      
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10

When we do this, the default step of 1 is used.