SQL Server - Msg 443 Invalid use of a side-effecting operator 'rand' within a function

Views: 333
Reading Time: 3 minutes

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:

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:

To generate random data from a resultset, you could do something like this:

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:

And the result of the execution like this:

Let's test now by generating the dummy data for testing:

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!