Olá pessoal,
Boa tarde!
No post de hoje vou demonstrar como utilizar sequences em user defined functions como scalar, table-valued e aggregate. Não sabe o que é uma SEQUENCE? Saiba mais sobre esse objeto acessando o post Trabalhando com Sequences no SQL Server
Por padrão, o SQL Server não permite que as propriedades NEXT VALUE FOR da sequence seja utilizada dentro de funções, uma vez que um dos conceitos da função é não ter acesso para alterar dados externos, ou seja, fora do escopo da função.
Mensagem de erro ao tentar utilizar SEQUENCE em função:
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.
Ao utilizar o NEXT VALUE FOR da sequence, estamos alterando a sequence, incrementando o valor da sequência e retornando essa informação. Sendo assim, ao fazer isso, estamos “burlando” e “infringindo” esse conceito da função. Entretanto, em algumas situações isso pode ser necessário, principalmente quando se já tem um sistema funcionando em produção e esse recurso precisa ser implementado com o mínimo de possibilidades de afetar o fluxo normal de funcionamento desse sistema.
Devido a essa necessidade, vou demonstrar como utilizar a sequence mesmo dentro de funções.
Como utilizar SEQUENCE em funções
Para conseguir utilizar uma SEQUENCE dentro de uma função, precisamos criar uma VIEW, utilizando uma instrução OPENROWSET, que irá abrir uma nova conexão na instância, chamar o NEXT VALUE FOR na sequence e retornar na view.
Dessa forma, a nossa função estaria realizando um simples select em uma view, mas em background estaríamos utilizando e consumindo uma SEQUENCE.
Criação da Sequence:
|
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 |
Criação da VIEW:
|
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 |
Criação da função:
|
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() |
Exemplo:
É isso aí, pessoal!
Espero que tenham gostado dessa dica. Não recomendo a sua utilização no dia a dia, mas dependendo da situação, pode ser uma dica salvadora.. rs
Abraço!
Portuguese 
English