Olá pessoal,
Boa tarde.
Neste post, vou demonstrar como contornar uma limitação do SQL Server, que é o uso da função RAND() em funções UDF, de forma que você possa gerar valores aleatórios e aplicar em uma tabela. Caso você tente fazê-lo, o SQL Server irá retornar essa mensagem de erro:
Msg 443, Level 16, State 1, Procedure fncGera_Senha, Line 50
Invalid use of a side-effecting operator ‘rand’ within a function.
Para contornar isso, existem várias formas, mas vou citar as 2 mais simples logo abaixo.
Criar uma view retornando a função RAND()
Uma alternativa simples para contornar esse problema, é criar uma view utilizando a função RAND(), que pode ser acessada pela função UDF. Veja abaixo como fazer:
|
1 2 3 |
CREATE VIEW dbo.vwRand AS SELECT RAND() AS [Rand] GO |
Em um uso mais simples. basta alterar a sua função UDF para que ao invés de utilizar a função RAND(), você faça um SELECT [rand] FROM vwRand.
Exemplo:
|
1 2 |
-- Número aleatório entre 0 e 10, podendo ser utilizado dentro de função UDF SELECT CAST((SELECT [rand] FROM vwRand) * 10 AS INT) |
Para gerar dados aleatórios a partir de um resultset, você poderia fazer algo assim:
|
1 2 3 4 5 6 7 8 9 |
SELECT DATEADD(DAY, (SELECT [Rand] FROM vwRand) * 12000, '1980-01-01') AS Dt_Nascimento, DATEADD(DAY, (SELECT [Rand] FROM vwRand) * 12000, '1980-01-01') AS Dt_Nascimento2, DATEADD(DAY, (SELECT [Rand] FROM vwRand) * 12000, '1980-01-01') AS Dt_Nascimento3, DATEADD(DAY, (SELECT [Rand] FROM vwRand) * 12000, '1980-01-01') AS Dt_Nascimento4, RIGHT(REPLICATE('0', 11) + CAST(CAST((SELECT [Rand] FROM vwRand) * 99999999999 AS BIGINT) AS VARCHAR(11)), 11) AS CPF1, RIGHT(REPLICATE('0', 11) + CAST(CAST((SELECT [Rand] FROM vwRand) * 99999999999 AS BIGINT) AS VARCHAR(11)), 11) AS CPF2, RIGHT(REPLICATE('0', 11) + CAST(CAST((SELECT [Rand] FROM vwRand) * 99999999999 AS BIGINT) AS VARCHAR(11)), 11) AS CPF3, RIGHT(REPLICATE('0', 11) + CAST(CAST((SELECT [Rand] FROM vwRand) * 99999999999 AS BIGINT) AS VARCHAR(11)), 11) AS CPF4 |
Ficando os dados aleatórios sendo gerados desta forma:
Essa solução parecer ter resolvido o nosso problema. Parece..
Como vocês podem ver, para gerar 1 valor aleatório, essa solução atende perfeitamente nossa necessidade, mas ao utilizar uma tabela pré-existente para gerar vários registros aleatórios, o resultado não saiu conforme o esperado, pois apenas 1 registro aleatório foi gerado e os outros se repetem nas demais linhas da tabela.
Substituindo o uso da função RAND() na sua UDF
Essa solução é mais simples de ser implementada e é mais performática, mas vamos analisar se ela realmente satisfaz nossos requisitos. Ela consiste basicamente em criar uma nova função UDF com o código abaixo, que simule o comportamento aleatório da função RAND(), e utilizá-la sua UDF.
Código-fonte da nova função UDF que gera dados aleatórios:
|
1 2 3 4 5 6 7 8 9 10 |
IF (OBJECT_ID('dbo.fncRand') IS NOT NULL) DROP FUNCTION dbo.fncRand GO CREATE FUNCTION dbo.fncRand(@Numero BIGINT) RETURNS BIGINT AS BEGIN RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero END GO |
E o resultado da execução assim:
Vamos testar agora, gerando os dados fictícios para testes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/* Cálculo da data: 1 a 12.000 dias após 01/01/1980 (+- 32 anos) Cálculo do CPF: Número aleatório entre 0 e 99999999999 */ SELECT DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento, DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento2, DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento3, DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento4, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF1, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF2, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF3, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF4 |
E o nosso teste final, que é utilizar uma tabela pra gerar vários registros aleatórios:
Como vocês podem observar, utilizando essa nova UDF, foi possível gerar os dados aleatórios para N registros de uma tabela, ela é mais performática e seu uso também é mais simples.
Espero que tenham gostado dessa dica e até o próximo post.
Abraço!
Portuguese 
English