Using sequences in user defined functions in SQL Server

Views: 374
Reading Time: 2 minutes

Hello people,
Good afternoon!

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

By default, SQL Server does not allow sequence NEXT VALUE FOR properties to be used within functions, since one of the function's concepts is not having 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 fncTest, 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.

By using sequence's NEXT VALUE FOR, we are changing the sequence, incrementing the sequence value, and returning this information. Thus, in doing so, we are “cheating” and “breaking” this concept of function. However, in some situations this may be necessary, especially when a system is already in production and this feature needs to be implemented with the minimum of possibility of affecting the normal flow of that system.

Because of 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 inside 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 sequence, and return in view.

That way, our function would be simply selecting a view, but in the background we would be using and consuming a SEQUENCE.

Sequence Creation:

VIEW Creation:

Role Creation:

SQL Server - Sequence in Function

That's it folks!
I hope you enjoyed this tip. I do not recommend its use in everyday life, but depending on the situation, it can be a saving tip .. lol