How to use RAND() in SQL Server User Defined Functions (UDF)

RAND() function cannot be called directly from a user-defined function.
If you try to create a function using the following code:

CREATE FUNCTION ufnGetRandomNumber()
RETURNS DECIMAL(10,10)
AS
BEGIN
RETURN (SELECT RAND())
END
GO

…you will get an error message:

Msg 443, Level 16, State 1, Procedure ufnGetRandomNumber, Line 5
Invalid use of a side-effecting operator 'rand' within a function.

invalid_use_of_rand_error

To avoid the error create a view that calls the RAND() function:

CREATE VIEW Get_RAND
AS
SELECT RAND() AS MyRAND
GO

And use the view to call the RAND function indirectly:

CREATE FUNCTION ufnGetRandomNumber()
RETURNS DECIMAL(10,10)
AS
BEGIN
RETURN (SELECT MyRAND FROM Get_RAND)
END
GO

The function is now created successfully:

function_call_rand_from_view

To use this user-defined function:

SELECT dbo.ufnGetRandomNumber()

random_number_function

Read related blog post:
How to use NEWID() in SQL Server User Defined Functions (UDF)