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() |
É 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!