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:
| 1 2 3 4 5 | 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:
| 1 2 3 4 5 6 7 | 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 |
Role Creation:
| 1 2 3 4 5 6 7 8 9 10 | CREATE FUNCTION dbo.fncTeste() RETURNS INT AS BEGIN RETURN (SELECT Proximo_Valor FROM dbo.vwSeq_Teste) END GO SELECT dbo.fncTeste() |
Example:
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
Hug!
