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

November 20, 2012 by pinaldave

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.

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 @rndValueENDGO

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 rndViewAS
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 = rndResultFROM rndViewRETURN @rndValueENDGO

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 (http://blog.SQLAuthority.com)

1. on November 20, 2012 at 8:08 am | Reply Alphonso Jones

   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