Hey guys,
Good afternoon.

In this post, I will demonstrate how to get 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_Password, Line 50
Invalid use of a side-effecting operator ‘rand’ within a function.

To get around this, there are several ways, but I will mention the 2 simplest below.

Create a view by returning the RAND() function

A simple alternative to get 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:

CREATE VIEW dbo.vwRand
AS SELECT RAND() AS [Rand]
GO

In simpler usage. just change your UDF function so that instead of using the RAND() function, you do a SELECT [rand] FROM vwRand.

Example:

-- 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:

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

The random data is generated in this way:

This solution seems to have solved our problem. It seems...

As you can see, to generate 1 random value, this solution perfectly meets our needs, but when using a pre-existing table to generate several random records, the result did not come out as expected, as only 1 random record was generated and the others are repeated in the other rows of the table.

Replacing the use of the RAND() function in your UDF

This solution is simpler to implement and is more performant, but let's analyze 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 in your UDF.

Source code of the new UDF function that generates random data:

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 execution result looks like this:

Let's test it now, generating the fictitious data for testing:

/*
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 performant and its use is also simpler.

I hope you liked this tip and see you in the next post.
Hug!