Click on the banner to learn about and purchase my database training on Azure

Using sequences in user defined functions in SQL Server

Views: 1.311 views
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.

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, in doing so, we are “circumventing” and “infringing” this concept of function. However, in some situations this may be necessary, especially when you already have a system running in production and this feature needs to be implemented with the least chance of affecting the normal flow of operation 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