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)

If the post helped you, please share it:
Pin It

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

  • Robert Carnegie

    This appears to answer a question which was asked at https://dba.stackexchange.com/questions/33364/rand-in-t-sql-function
    so I have borrowed it to there, which I hope is acceptable.

    I have used the "view" trick to incorporate the value of GETDATE() where a "nondeterministic" function is not allowed - but I wonder if these tricks are dangerous (or maybe inefficient), since it is working around the server preventing you from using the formula explicitly.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">