Hey guys,
Good afternoon!

In today's post I will demonstrate how to use sequences in user defined functions such as scalar, table-valued and aggregate. Don't know what a SEQUENCE is? Find out more about this object by accessing the post Working with Sequences in SQL Server

By default, SQL Server does not allow sequence's NEXT VALUE FOR properties to be used within functions, since one of the concepts of the function is not to have access to change external data, that is, outside the scope of the function.

Error message when trying to use SEQUENCE in function:
Msg 11719, Level 15, State 1, Procedure fncTeste, Line 13
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, derived tables or return statements.

When using the NEXT VALUE FOR of the sequence, we are changing the sequence, increasing the value of the sequence and returning this information. Therefore, by doing this, we are “circumventing” and “infringing” on this concept of function. However, in some situations this may be necessary, especially when you already have a system working in production and this feature needs to be implemented with the minimum possibility of affecting the normal flow of operation of that system.

Due to this need, I will demonstrate how to use sequence even within functions.

How to use SEQUENCE in functions

To be able to use a SEQUENCE within a function, we need to create a VIEW, using an OPENROWSET statement, which will open a new connection in the instance, call NEXT VALUE FOR in the sequence and return in the view.

This way, our function would be performing a simple select in a view, but in the background we would be using and consuming a SEQUENCE.

Sequence Creation:

IF (OBJECT_ID('dbo.seq_Teste') IS NOT NULL) DROP SEQUENCE dbo.seq_Teste
CREATE SEQUENCE dbo.seq_Teste AS INT
START WITH 1
INCREMENT BY 1
GO

VIEW creation:

IF (OBJECT_ID('dbo.vwSeq_Teste') IS NOT NULL) DROP VIEW dbo.vwSeq_Teste
GO

CREATE VIEW dbo.vwSeq_Teste
AS
SELECT [Proximo_Valor] FROM OPENROWSET('SQLOLEDB', 'SERVER=localhost;TRUSTED_CONNECTION=yes', 'SET FMTONLY OFF; SELECT NEXT VALUE FOR Database.dbo.seq_Teste AS [Proximo_Valor]')
GO

Function creation:

CREATE FUNCTION dbo.fncTeste()
RETURNS INT
AS BEGIN

	RETURN (SELECT Proximo_Valor FROM dbo.vwSeq_Teste)
	
END
GO

SELECT dbo.fncTeste()

Example:

SQL Server - Sequence in Function
SQL Server - Sequence in Function

That's it, folks!
I hope you liked this tip. I don't recommend using it on a daily basis, but depending on the situation, it could be a life-saving tip... lol

Hug!