|
|
Error Message
Server: Msg 11719, Level 15, State 1, Line 1
NEXT VALUE FOR function is not allowed in check
constraints, default objects, computed columns,
views, user-defined functions, user-defined aggregates,
user-defined table types, sub-queries, common table
expressions, or derived tables.
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. The NEXT VALUE FOR is not allowed to be used in views, in user-defined functions, or in computed columns. Including it in a user-defined function will raise this error message.
Here’s a script that shows the NEXT VALUE FOR used in a user-defined function and thus generating this error message:
CREATE SEQUENCE [dbo].[JusticeLeagueSequence] AS INT
START WITH 1000
GO
CREATE SEQUENCE [dbo].[AvengerSequence] AS INT
START WITH 2000
GO
CREATE FUNCTION [dbo].[ufn_GetNextSequence] ( @Type CHAR(1) )
RETURNS INT
AS
BEGIN
DECLARE @Sequence INT
IF @Type = 'J'
SET @Sequence = NEXT VALUE FOR [dbo].[JusticeLeagueSequence]
ELSE
SET @Sequence = NEXT VALUE FOR [dbo].[AvengerSequence]
RETURN @Sequence
END
GO
Msg 11719, Level 15, State 1, Procedure ufn_GetNextSequence, Line 9
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table
expressions, or derived tables.
Solution / Work Around:
To overcome the limitation of the use of NEXT VALUE FOR in user-defined functions, it will involve the creation of a view that will return the NEXT VALUE for the sequence object. Unfortunately, one of the other limitations of the use of NEXT VALUE FOR is that it is also not allowed to be used in a view. To overcome this limitation, the OPENROWSET rowset function can be used. The OPENROWSET is an alternative to accessing tables in a linked server, or in this case, the local server, and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.
Here’s how the statement will look like that indirectly uses the NEXT VALUE FOR statement in a view definition overcoming the limitation with the help of the OPENROWSET rowset function followed by the use of the view in a user-defined function, thus overcoming the limitation of using the NEXT VALUE FOR statement in a user-defined function:
CREATE VIEW [dbo].[JusticeLeagueSequenceView]
AS
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQLServerHelper;UID=sqlserver;Pwd=SQLPassword',
'SELECT NEXT VALUE FOR [dbo].[JusticeLeagueSequence] AS [SequenceID]')
GO
CREATE VIEW [dbo].[AvengerSequenceView]
AS
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQLServerHelper;UID=sqlserver;Pwd=SQLPassword',
'SELECT NEXT VALUE FOR [dbo].[AvengerSequence] AS [SequenceID]')
GO
CREATE FUNCTION [dbo].[ufn_GetNextSequence] ( @Type CHAR(1))
RETURNS INT
AS
BEGIN
DECLARE @Sequence INT
IF @Type = 'J'
SELECT @Sequence = [SequenceID]
FROM [dbo].[JusticeLeagueSequenceView]
ELSE IF @Type = 'A'
SELECT @Sequence = [SequenceID]
FROM [dbo].[AvengerSequenceView]
RETURN @Sequence
END
GO
Here is a sample output from the user-defined function:
SELECT [dbo].[ufn_GetNextSequence] ( 'J' ) AS [JusticeLeagueSequence],
[dbo].[ufn_GetNextSequence] ( 'A' ) AS [AvengerSequence]
JusticeLeagueSequence AvengerSequence
---------------------- ----------------
1000 2000
|