Create generate random number function in SQL Server

Question

I'm trying to create the function in SQL Server. In this function I have generated the random number, but function not generated.

Create function [GetRandomNumber]
(
)
RETURNS bigint
as 
Begin
Declare @randomNo int
 set  @randomNo = (select round(rand(checksum(newid()))*(10001)+50000,0) as [GetRandomNumber])
 return @randomNo
End

this is generated in following error:

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

Answer 1

You can. However, it will require a little bit of extra legwork.

First, you need to create a view, like the one below:

create view dbo.sys_NDF
as
select rand() as [ValueRand], newid() as [ValueGUID],
  rand(checksum(newid())) as [SeededRand];
go

The trick is that you cannot call these system functions directly from your UDF, however you can query a view that returns their values. You can later expand it with other functions / columns if need be.

As such, your function starts to look like the following:

Create function [GetRandomNumber]()
RETURNS bigint as begin
return (select round(v.SeededRand * 10001 + 50000, 0) from dbo.sys_NDF v);
end;
go