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.

As the message said, you can't do that. pass in the newid() as a parameter — Squirrel
– Squirrel, Commented Jul 9, 2019 at 10:57
Except for CLR functions, user-defined functions must be deterministic. For that reason, you can't write a function to produce a random number. You could write a stored procedure to do so -- or you could just use CONVERT(INT, CRYPT_GEN_RANDOM(4)) (or BIGINT and 8, your return type and expression contradict each other). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 9, 2019 at 10:58
@JeroenMostert no any way to i have to create the generate the random number in function. — Dip Girase
– Dip Girase, Commented Jul 9, 2019 at 11:01
Then your only recourse (if you don't want hacky half-solutions like passing in the non-deterministic part, which is easy to get wrong) is a non-deterministic CLR function, as stated before. Building and installing these is not entirely trivial, though (but as a bonus, generating a good random number in a range is far easier in C#). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 9, 2019 at 11:03
Well, there's one work-around I can think off. But it's slightly ugly. Pass a random float as a variable to the function via f.e. RAND(). Then use that variable in the function. — LukStorms
– LukStorms, Commented Jul 9, 2019 at 11:22

Roger Wolf · Accepted Answer · 2019-07-09 11:36:32Z

3

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

edited Jul 9, 2019 at 11:36

answered Jul 9, 2019 at 11:28

Roger Wolf

7,7972 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dip Girase Over a year ago

i am just check in function in possible or not to do this.

Khairul Alam · Accepted Answer · 2019-07-09 11:30:56Z

1

Create SP instead of function. Because some system function are not allowed in user defined function.

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

GO
 DECLARE @returnvalue INT
 EXEC @returnvalue = GetRandomNumber
 SELECT @returnvalue

answered Jul 9, 2019 at 11:30

Khairul Alam

1,3462 gold badges13 silver badges33 bronze badges

1 Comment

Ian Boyd Over a year ago

The problem there is that you cannot OUTER APPLY to a stored procedure.

MattM · Accepted Answer · 2024-07-17 05:48:12Z

Along the lines of @LukStorms suggestion in the comments of the original question, you can create a function this is deterministic and gets its randomness from passing RAND() as a parameter.

CREATE OR ALTER FUNCTION [GetRandomNumber]
(
    @NumberOfDigits AS TINYINT
    , @RandomSeed   AS FLOAT
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @RandomInteger  AS BIGINT;
    SELECT  @RandomInteger  = CAST ((@RandomSeed * (POWER (10, @NumberOfDigits))) AS BIGINT)
    RETURN  @RandomInteger;
END;
GO

Then execute the query:

SELECT  [GetRandomNumber] (3, RAND()) AS [random_3_digit_number]
        , [GetRandomNumber] (5, RAND()) AS [random_5_digit_number]
        , [GetRandomNumber] (8, RAND()) AS [random_8_digit_number];

But then, if you're going to all the trouble of using a RAND() function anyway, why not do so from the query:

SELECT  CAST (RAND() * 1000 AS BIGINT) AS [random_3_digit_number]
        , CAST (RAND() * 100000 AS BIGINT) AS [random_5_digit_number]
        , CAST (RAND() * 100000000 AS BIGINT) AS [random_8_digit_number];

Collectives™ on Stack Overflow

Create generate random number function in SQL Server

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related