«

»

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. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>