Use RAND() in User Defined Function

Question

I am trying to create a user defined function which calls the system RAND() function inside it, when I try to create the function it errors out with the following message:

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

My function code:

CREATE FUNCTION getNumber(@_id int)
RETURNS DECIMAL(18,4)
AS
BEGIN
   DECLARE @RtnValue DECIMAL(18,4);

   SELECT TOP 1 @RtnValue = EmployeeID 
   FROM dbo.Employees
   ORDER BY EmployeeID DESC

   SET @RtnValue = RAND() * @RtnValue * (1/100)

   RETURN @RtnValue;
END

How can I fix this please?

Answer 1

The problem is that you cannot call a non-deterministic function from inside a user-defined function.

I got around this limitation by creating a view, call that function inside the view and use that view inside your function, something like this......

View Definition

CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value

Function Definition

ALTER FUNCTION getNumber(@_id int )
RETURNS DECIMAL(18,4)
AS
BEGIN
   DECLARE @RtnValue DECIMAL(18,4);
   SELECT TOP 1 @RtnValue = EmployeeID 
   FROM dbo.Employees
   ORDER BY EmployeeID DESC

   SET @RtnValue = (SELECT Value FROM vw_getRANDValue) * @RtnValue * (1.0000/100.0000) --<-- to make sure its not converted to int
    RETURN @RtnValue;
END