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

Answer 2

Just pass RAND() value as parameter from outside:

CREATE FUNCTION getNumber(@_id int, @RAND FLOAT)
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

And call as getNumber(10, RAND())

No any side effects.

Answer 3

be carefull with RAND!

If you check this, you'll see, that multiple calls to this VIEW come all back with the same value. This is different with NEWID(). So if you really want random numbers it could be better to take NEWID() und do some "tricks" to get a number from - let's say - the first bytes...

CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value
GO
CREATE VIEW vw_getNEWID
AS
SELECT NEWID() AS Value
GO
CREATE FUNCTION dbo.Test() 
RETURNS TABLE AS
RETURN
WITH Numbers AS
(SELECT 1 AS x UNION SELECT 2 UNION SELECT 3) 
SELECT *
     ,(SELECT Value FROM vw_getRANDValue) AS myRandom
     ,(SELECT Value FROM vw_getNEWID) AS myNewid
FROM Numbers
GO
SELECT * FROM dbo.Test();
GO
DROP FUNCTION dbo.Test;
GO
DROP VIEW vw_getRANDValue;
GO
DROP VIEW  vw_getNEWID;
GO      

This is a result:

1. 0,684530884058892 D1809581-BBD1-4D23-A7F9-BC697E869BB0
2. 0,684530884058892 A4BAECDE-E993-46C1-B571-7440A713C371
3. 0,684530884058892 D7A1CB65-D2BC-41B2-990D-C3BC52B056A2

A view for a random BIGINT could look like this:

CREATE VIEW vw_getRandomBigInt
AS
SELECT CONVERT(BIGINT,CONVERT(VARBINARY(16),NEWID(),1)) * (-1) AS Value
GO

Hint: I checked this with many rows and it seems (just by sight), that this approach is not really random (all BIGINTs have the same width...). This seems to work properly:

CREATE VIEW vw_getRandomInt
AS
SELECT sys.fn_replvarbintoint(sys.fn_cdc_hexstrtobin(LEFT(REPLACE(CONVERT(VARCHAR(100),NEWID()),'-',''),4))) AS Value
GO

Answer 4

One more idea: Use the function just to do the calculation for your business logic and hand in the non-deterministic part. In your case you seem to pick a random number between zero and the highest employeeID (what about missing IDs?)

As told before RAND() is difficult to use. It will come back with the same value in multiple calls. Therefore I use NEWID, cast it to VARBINARY(8) and cast this to BIGINT.

Have a look at this:

This function will take a GUID and scale it between the given borders:

CREATE FUNCTION dbo.GetRandomNumber(@lowerLimit BIGINT, @upperLimit BIGINT, @GuidValue UNIQUEIDENTIFIER)
RETURNS BIGINT
AS
BEGIN
    RETURN
    (
    SELECT ABS(CAST(CAST(@GuidValue AS VARBINARY(8)) AS BIGINT)) % (@upperLimit-@lowerLimit)+@lowerLimit
    )
END
GO

--This table will be filled with random values

CREATE TABLE testTable(RndNumber BIGINT,Tile INT);

--The CTE creates more than 6 mio dummy rows

WITH manyRows AS
(
    SELECT 1 AS nr FROM master..spt_values CROSS JOIN master..spt_values AS x
)
INSERT INTO testTable(RndNumber) 
SELECT dbo.GetRandomNumber(-300,700,NEWID()) --<-- Here I pass in the non-deterministic part
FROM manyRows;

--Now the table is tiled in 10 equal fragments

WITH UpdateableCTE AS
(
    SELECT Tile
          ,NTILE(10) OVER(ORDER BY RndNumber) AS tileValue
    FROM testTable
)  
UPDATE UpdateableCTE SET Tile=tileValue;

--check the random result

SELECT * FROM testTable
ORDER BY Tile,RndNumber;

--This shows clearly, that the covered range per tile is almost the same which is a proof for a fairly good random spread

SELECT Tile
      ,COUNT(*) CountOfNumbers
      ,MAX(RndNumber)-MIN(RndNumber) CoveredRange
FROM testTable
GROUP BY Tile
ORDER BY Tile;
GO

--clean up

DROP TABLE dbo.testTable;
DROP FUNCTION dbo.GetRandomNumber;

The result

T   Counts  min     max     CoveredRange
1   636553  -300    -201      99
2   636553  -201    -101     100
3   636553  -101       0     101
4   636553     0      99      99
5   636553    99     199     100
6   636553   199     299     100
7   636553   299     399     100
8   636553   399     499     100
9   636553   499     599     100
10  636552   599     699     100

You can see, that each tile covers roughly the same count of elements. The elements inside are covering almost the same range. This shows, that the numbers are evenly spread within the table.

Answer 5

you cannot use RAND function inside a function instead you can create simple View of Rand function and use it inside the function. This is just a workaround

View :

CREATE VIEW random_val_view
AS
SELECT RAND() as  random_value

Function :

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 = (select random_value from random_val_view) * @RtnValue * (1/100.0)
    RETURN @RtnValue;
END

Answer 6

You can get random numbers or chars with these scripts from Views (from SQL 2017):

-- FLOAT BETWEEN 0 AND 1
CREATE VIEW [dbo].[GetRandomFloat]
AS
    SELECT  CAST('0.'+REPLACE(TRANSLATE(NEWID(), 'ABCDEF-', '#######'),'#','') AS FLOAT) AS RND
GO

-- INT FROM 0 TO 9
CREATE VIEW [dbo].[GetRandomFrom0To9]
AS
    SELECT  CAST(COALESCE(LEFT(REPLACE(TRANSLATE(NEWID(), 'ABCDEF-', '#######'), '#', ''), 1), '0') AS TINYINT) AS RND
GO

-- CHAR FROM 'A' TO 'Z'
CREATE VIEW [dbo].[GetRandomFromAToZ]
AS
    SELECT CHAR(CAST(ROUND(CAST('0.'+REPLACE(TRANSLATE(NEWID(), 'ABCDEF-', '#######'),'#','') AS FLOAT) * 25 + 65, 0) AS TINYINT)) AS RND
GO

The SELECT:

SELECT [RND] FROM [GetRandomFloat]
SELECT [RND] FROM [GetRandomFrom0To9]
SELECT [RND] FROM [GetRandomFromAToZ]