SQL Server Error Messages - Msg 11741

Error Message

Server: Msg 11741, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Causes

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence object was created.

The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle or repeat as requested. Sequences, unlike identity columns, are not associated with tables.

A sequence is created independently of table objects by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance.

The syntax of the CREATE SEQUENCE statement is as follows:

CREATE SEQUENCE <Sequence Name> AS <integer_type>
 [ START WITH <constant> ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
 [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]

To generate a sequence number from a specified sequence object, the NEXT VALUE FOR metadata function is used. Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table.

The NEXT VALUE FOR function is a non-deterministic function, and is only allowed in contexts where the number of generated sequence values is well defined. There are a lot of limitations and restrictions with the use of the NEXT VALUE FOR. One of these restrictions is in conditional expressions such as CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF, which the NEXT VALUE FOR function is not allowed to be used.

Using the following sequence object definition:

CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 0 INCREMENT BY 1
GO

Here are a few examples of how this error message can be encountered when used in a CASE, IIF and NULLIF statements:

-- Case #1 : CASE Statement
SELECT CASE WHEN NEXT VALUE FOR [dbo].[SQLSequence] % 2 = 0
            THEN 'Even' ELSE 'Odd' END AS [OddEven]

Msg 11741, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

-- Case #2 : IIF Logical Function
SELECT IIF(NEXT VALUE FOR [dbo].[SQLSequence] % 2 = 0, 'Even', 'Odd') AS [OddEven]

Msg 11741, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

-- Case #3 : NULLIF Function
SELECT NULLIF(NEXT VALUE FOR [dbo].[SQLSequence], 0)

Msg 11741, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Solution / Work Around

One way to work around the limitation of the use of the NEXT VALUE FOR in a CASE statement, IIF logical function and NULLIF function is to first put the result of the NEXT VALUE FOR in a local variable and use that local variable in the CASE, IIF and NULLIF functions, as can be seen in the following scripts:

-- Case #1 : CASE Statement
DECLARE @Value    INT

SET @Value = NEXT VALUE FOR [dbo].[SQLSequence]
SELECT CASE WHEN @Value % 2 = 0
            THEN 'Even' ELSE 'Odd' END AS [OddEven]
GO

-- Case #2 : IIF Logical Function
DECLARE @Value    INT

SET @Value = NEXT VALUE FOR [dbo].[SQLSequence]
SELECT IIF(@Value % 2 = 0, 'Even', 'Odd') AS [OddEven]
GO

-- Case #3 : NULLIF Function
DECLARE @Value    INT

SET @Value = NEXT VALUE FOR [dbo].[SQLSequence]
SELECT NULLIF(@Value, 0) AS [SequenceValue]
GO

According to the error message, the NEXT VALUE FOR is not allowed to be used in a CHOOSE logical function. However, using the following script, it can be seen that the NEXT VALUE FOR is allowed in the conditional expression of the CHOOSE logical function:

-- CHOOSE works
CREATE SEQUENCE [dbo].[DaySequence] AS TINYINT
START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 7 CYCLE
GO

SELECT CHOOSE(NEXT VALUE FOR [dbo].[DaySequence],
             'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
       AS [DayOfTheWeek]
GO

Executing the SELECT statement above will not generate an error but return a result instead.