Dec 13 2011

Using RAND() in user defined function

Ever wanted to use a random number in a TSQL UDF? And getting an error message like this:

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

There’s a quick (and dirty) solution for this.

In T-SQL, the good and quick solutions often get dirty, so I’m not worried about that.

Create a view like this:

CREATE VIEW dbo.RandView AS SELECT RAND() AS Value

And then you can select the value in your function:

DECLARE @rnd NUMERIC(18,10)

SELECT TOP 1 @rnd = Value FROM dbo.RandView

-- your code...

Leave a Reply

Your email address will not be published.