Hello people,
Good afternoon.
In this post, I will demonstrate how to work around a limitation of SQL Server, which is the use of the RAND () function in UDF functions, so that you can generate random values and apply them to a table. If you try to do so, SQL Server will return this error message:
Msg 443, Level 16, State 1, Procedure fncGera_Senha, Line 50
Invalid use of a side-effecting operator 'rand' within a function.
To get around this, there are several ways, but I'll quote the simplest 2 below.
Create a view by returning the RAND () function
A simple alternative to work around this problem is to create a view using the RAND () function, which can be accessed by the UDF function. See below how to do it:
|
1 2 3 |
CREATE VIEW dbo.vwRand AS SELECT RAND() AS [Rand] GO |
In a simpler use. Just change your UDF function so that instead of using the RAND () function, you make a SELECT [rand] FROM vwRand.
Example:
|
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) |
To generate random data from a resultset, you could do something like this:
|
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 |
Getting the random data being generated like this:
This solution seems to have solved our problem. Looks..
As you can see, to generate 1 random value, this solution perfectly meets our need, but when using a pre-existing table to generate multiple random records, the result did not go as expected because only 1 random record was generated and the others are repeated in the other rows of the table.
Overriding the use of the RAND () function in your UDF
This solution is simpler to implement and more performative, but let's look at whether it really meets our requirements. It basically consists of creating a new UDF function with the code below, which simulates the random behavior of the RAND () function, and using it as its UDF.
Source code of the new UDF function that generates random data:
|
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 |
And the result of the execution like this:
Let's test now by generating the dummy data for testing:
|
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 |
And our final test, which is to use a table to generate several random records:
As you can see, using this new UDF, it was possible to generate random data for N records in a table, it is more performative and its use is also simpler.
I hope you enjoyed this tip and see you in the next post.
Hug!
