SQL Server - Generate Random number using User Defined Functions

Calling RAND function in a user defined Function:

RAND() function in SQL Server returns random number between 0 and 1.

What happens when we call RAND function in a Function?

Creating an user-defined function to generate Random value:

Create function dbo.Fn_GenRand(@a int)

returns float
as
begin
return(select rand())
end

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

While creating above function, we get error message by using RAND() function. Because RAND() function is of Non-Deterministic function.

In SQL Server, USER defined functions are basically divided into 2 types. 

1. Deterministic Functions always returns same output when they called with specific set of input values in the same state of database.

SQRT,ISNULL,ISNUMERIC,POWER,DATEDIFF,CEILING,FLOOR,DATEADD,DAY,MONTH,YEAR,SQUARE etc.

2. Non-Deterministic Functions always returns different output when they called with specific set of input values in the same state of database.

GETDATE,ROW_NUMBER,RAND,@@MAX_CONNECTIONS,@@CONNECTIONS,RANK,NEWID, @@TOTAL_WRITE etc.

How to use get Random Value using Function?

1. Create a view:

Create view [dbo].[vw_genrandom]
as

SELECT RAND(convert(varbinary,NEWID()))*100  AS val

2. Create function:

Create function dbo.Fn_GenRand(@a int)
returns float
as
begin
return(select * from vw_genrandom)*@a

end

3. Generate Random Number:

select  dbo.Fn_GenRand(10)

Output