SQL SERVER – Using RAND() in User Defined Functions (UDF)

Here is the question I received in email.

“Pinal,

I am writing a function where we need to generate random password. While writing T-SQL I faced following issue. Everytime I tried to use RAND() function in my User Defined Function I am getting following error:

Msg 443, Level 16, State 1, Procedure RandFn, Line 7
Invalid use of a side-effecting operator ‘rand’ within a function.

Solarwinds

Here is the simplified T-SQL code of the function which I am using:

CREATE FUNCTION RandFn()
RETURNS INT
AS
BEGIN
DECLARE @rndValue INT
SET @rndValue = RAND()
RETURN @rndValue
END
GO

I must use UDF so is there any workaround to use RAND function in UDF.”

Here is the workaround how RAND() can be used in UDF. The scope of the blog post is not to discuss the advantages or disadvantages of the function or random function here but just to show how RAND() function can be used in UDF.

RAND() function is directly not allowed to use in the UDF so we have to find alternate way to use the same function. This can be achieved by creating a VIEW which is using RAND() function and use the same VIEW in the UDF. Here is the step by step instructions.

Create a VIEW using RAND function.

CREATE VIEW rndView
AS
SELECT RAND() rndResult
GO

Create a UDF using the same VIEW.

CREATE FUNCTION RandFn()
RETURNS DECIMAL(18,18)
AS
BEGIN
DECLARE @rndValue DECIMAL(18,18)
SELECT @rndValue = rndResult
FROM rndView
RETURN @rndValue
END
GO

Now execute the UDF and it will just work fine and return random result.

SELECT dbo.RandFn()
GO

In T-SQL world, I have noticed that there are more than one solution to every problem. Is there any better solution to this question? Please post that question as a comment and I will include it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Removing Leading Zeros From Column in Table
Next Post
SQL SERVER – Removing Leading Zeros From Column in Table – Part 2

Related Posts

17 Comments. Leave new

  • You can also use a stored procedure with an output parameter:

    Create procedure dbo.usp_Randomnumber @Somenumber float OUTPUT

    as
    Begin
    Set @Somenumber = rand()
    End
    Go

    Declare @Number float
    Exec dbo.usp_Randomnumber @Number OUTPUT
    Select @Number

    Reply
  • go
    create function testRand(@rand1 float)
    returns bigint
    as
    begin
    — your logic to manipulate random value
    return cast((@rand1*100000) as bigint)
    end
    go
    select dbo.testRand(rand())
    –cleanup
    drop function dbo.testRand

    Reply
  • I am convinced there is a reason for needing udf instead of just using rand() directly. The request as is doesn’t make much sense. If a udf is required, maybe consider a CLR if option is available.

    Reply
  • pitoach - Ronen Ariely
    September 4, 2013 5:32 pm

    you can use: ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))
    as random number inside a function or directly as random function.

    * I have tested using SQLQueryStress, using 2 threads and 100000 Iterations those two queries and i got that using NEDWID is much faster & Much less CPU. i am not sure if this will be the case for any use, I actually did not expect these results, as do a large number of “type converting” used when using NEWID (this is not as a result of using int Vs float… i have tried the function using int in the view by using “SELECT CONVERT(int, RAND()*10000)” in the view).

    Query 1: select [dbo].[RandFn]() AS [RandomNumber] from dbo.[MyTable]
    Query 2: select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber] from dbo.[MyTable]

    Reply
  • pitoach - Ronen Ariely
    September 4, 2013 6:43 pm

    Correction!
    “as random number inside a function or directly as random function.”
    should be
    “as random number Instead of a function and use it directly as random function.”
    as NEWID is side-effecting operator too :-)

    Reply
  • Lazaro De Almeida
    September 24, 2013 1:18 am

    There’s a easier way to solve this kind of problem. Just use the function Rand() as a parameter.

    CREATE FUNCTION RandFn(@RAND FLOAT)
    RETURNS INT
    AS
    BEGIN
    DECLARE @rndValue INT
    SET @rndValue = @RAND
    RETURN @rndValue
    END
    GO

    SELECT dbo.RandFn(Rand())
    GO

    Reply
  • André Luis Nesso
    November 14, 2013 1:25 am

    Thank very much, excellent tip.

    Reply
  • chitransh srivastav
    February 28, 2016 5:06 pm

    select rand() knt
    go 5

    what will be the result?
    please let me know the answer

    Reply
  • Hennie de Nooijer
    May 17, 2016 5:25 pm

    Great tip.

    Reply
  • In a set how much iteration required to repeat the same number ?

    Reply
  • dependent on what you’re using the random number for you could always use datepart(ms,getdate()) in your user function.

    Reply

Leave a Reply

Menu