Nov 16

Written by: willgillen
11/16/2006

Wow, I was beating my head on the wall trying to figure out how to use the ‘rand()’ function within an MSSQL UDF (User Defined Function).  We had recently been working on a project for a client, and I had written an elegant random password generator, and was ready to implement it.  However, once I tried to test the UDF MSSQL started throwing an error stating “invalid use of the rand() function inside a user defined function.”

I read and read and read (and used Google occaisionally), and every where I looked, most professionals simply noted that according to Microsoft Books Online, you CANNOT use the rand() function inside a UDF. Bummer!

Then I ran across this posting on GoogleGroups

Basically, Steve Kass implements a very nice work-around that I think works quite well.  Basically, you move the rand() call into a “View”, and then use that newly created “View” to generate your random number from inside the function!!!  See below as I extracted from the post (it worked beautifully):

create view myRand
as select rand( ) as r
go
CREATE FUNCTION dbo.ReturnRand ( )
RETURNS real
AS
BEGIN
 declare @r real
 set @r = (select r from myRand)
 return @r
END
GO
SELECT dbo.ReturnRand ( )
SELECT dbo.ReturnRand ( )
GO


Now, you can use that view in your User Defined Function instead of the Rand() reserved function.

Tags: