SQL Server Portal

Sequence is one of the nice features shipped with SQL Server 2012 and it enhances a lot of identity column features and provides hands on a lot of things that identity does not support. But on the other hand it has its own limitations as well. Today, I was working on one of the user defined functions in my application using SEQUENCE and I came across an error.

Let me explain this error in detail :

Message Number: 11724

Severity : 15

Error Message: An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.

Error Generation:

Let me create a User defined function and a Sequence to demonstrate this error.

USE tempdb
GO

--Create a sequence
CREATE SEQUENCE [dbo].[Seq_Sequence]
AS [int]
START WITH 1
GO

--Create a user defined function
CREATE FUNCTION [dbo].[fn_Sample]
(
@SequenceID INT
)
RETURNS TABLE AS RETURN
(
SELECT 'RCP' + TRY_Convert(varchar(10),@SequenceID) AS [ID]
, GETDATE() AS [CREATION DATETIME]
)
GO

The purpose to create the Sequence object and the user defined function is basically I want to get the new ID from sequence and pass it to the user defined function to FORMAT and return it with creation date time.
It seems very simple, lets do it.

USE tempdb
GO
SELECT * FROM [dbo].[fn_Sample]
(
NEXT VALUE FOR [Seq_Sequence]
)
--OUTPUT

Msg 11724, Level 15, State 1, Line 1
An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function

Ooopps…… I am unable to execute it.

Resolution:

The resolution is very simple, NEVER pass the NEXT VALUE of sequence directly to table value user defined function. Just introduce a variable, store the NEXT VALUE in it and pass the variable into the user defined function.
Lets do it.

USE tempdb
GO
DECLARE @ID AS BIGINT = NEXT VALUE FOR [Seq_Sequence]
SELECT * FROM [dbo].[fn_Sample](@ID)
--OUTPUT

Conclusion :

Remember, whenever you use SEQUENCE object with any other object always be careful about the limitations of sequence object.