Using RAND() within user defined functions (UDF)

[Lewis Edward Moten III]

Notice - this is only an example of a scenario relating to the
functionality I am trying to achieve. There are many places where I
am trying to generate radomized information. This has been simplified
for discussion.

Target:
- Create a random string between X and Y characters long.

Having problems with random function within SQL Server. Seems others
on the groups have mentioned there are deterministic and
non-deterministic functions. RAND() can't be used because it is
non-deterministic - in that it changes each time you read its return
value (just like GETDATE()).

For example, the following is invalid.

CREATE FUNCTION [dbo].[RandomNum]()RETURNS FLOAT AS
BEGIN
RETURN(Rand())
END

When I use the [Check Syntax] button, or try to save it, the following
message appears:

Server: Msg 443, Level 16, State 1, Procedure RandomNum, Line 3
Invalid use of 'rand' within a function.

I thought I would get a little smart and reference a procedure ...

CREATE PROCEDURE [dbo].[spRandomNum](@@Num FLOAT OUTPUT) AS SET @@Num
= rand()

and then call it from within the function:

CREATE FUNCTION [dbo].[RandomNum]() RETURNS FLOAT AS
BEGIN
DECLARE @@TheNum FLOAT
EXEC sprRandomNum @@TheNum OUTPUT
RETURN(@@TheNum)
END

When using [check syntax] button in enterprise manager, everything
checked out fine. No error messages this time. However, running a
query against the function proved otherwise unsuccessful.

SELECT dbo.RandomNum()

Server: Msg 557, Level 16, State 2, Procedure RandomNum, Line 4
Only functions and extended stored procedures can be executed from
within a function.

So next I tried referencing a DOS utility on the command line and try
accessing it with an extended stored procedure:

CREATE FUNCTION [dbo].[RandomFunction] ()
RETURNS FLOAT AS
BEGIN
insert into TempDOSResults Execute xp_command 'C:\Random.exe'
-- parse number from results to be returned here --
return(1)
END

Unfortunately I got: Invalid use of 'INSERT' within a function.

=============================

Conclusion ... It seems that every attempt to work-around the solution
brings up brick walls. I'll have to try and use the output parameters
from a stored procedure rather then reference a function directly.
Look at the following two lines to understand the solution. The first
is with procedures and takes 3 lines. The other is the method I would
prefer and only takes one line.

DECLARE @Password VARCHAR(10)
EXEC sprGeneratePassword 5, 10, @Password OUTPUT
UPDATE Users SET Password = @Password WHERE UserID = 1

UPDATE Users SET Password = dbo.funGeneratePassword(5, 10) WHERE
UserID = 1

Now the real trick is this ... I need to update everyones password. A
function will still leave me with just one line:

UPDATE Users SET Password = dbo.funGeneratePassword(5, 10)

However, using a stored procedure would require the introduction of
cursors and now takes 11 lines of code.

DECLARE @UserID INT
DECLARE @Password VARCHAR(10)
DECLARE User CURSOR For SELECT UserID FROM Users
OPEN User
FETCH NEXT FROM User INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sprGeneratePassword 5, 10, @Password OUTPUT
UPDATE Users SET Password = @Password WHERE UserID = @UserID
FETCH NEXT FROM User INTO @UserID
END

[Steve Kass]

Lewis,

You can get a random number from a udf this way:

CREATE FUNCTION fn_rand ()
RETURNS int
AS
BEGIN
DECLARE @r int
SET @r = (SELECT r
FROM OPENQUERY(DESKTOP,'SELECT cast(1000*RAND() as int) AS r'))
-- you may need to run sp_serveroption 'DESKTOP', 'data access', 'true'
-- and of course name your server correctly
RETURN @r
END
GO

This will have lousy performance, but it works. Otherwise, you may be best off
filling a table with random numbers to use for whatever you need. Just realize you
need to fill the table sequentially (with a loop), since rand() will only be evaluated
once in a query like select rand() from BigTable.

Steve Kass
Drew University

[oj]

heheheh...resort to openquery(), eh.

--
-oj

"Steve Kass" <sk...@drew.edu> wrote in message
news:3CD183D1...@drew.edu...

[BP Margolin]

Steve,

An alternative approach ... inspired by Richard Romley ...

create view v
as select rand( ) as r
go

CREATE FUNCTION dbo.ReturnRand ( )
RETURNS real
AS
BEGIN
declare @r real
set @r = (select r from v)
return @r
END
GO

SELECT dbo.ReturnRand ( )
SELECT dbo.ReturnRand ( )
GO

BPM

"Steve Kass" <sk...@drew.edu> wrote in message
news:3CD183D1...@drew.edu...

[Steve Kass]

Well, I just figured out how to write xp's with output parameters, so I won't be using openquery any more, I don't think.

Steve

[oj]

that's a nice simple trick!

--
-oj

"BP Margolin" <bpm...@attglobal.net> wrote in message
news:u4rOC1g8BHA.2596@tkmsftngp05...

[Steve Kass]

Much better. I've also just got this working. Lot's to play around with.

create function dbo.myRand()
returns real as begin
declare @r real
exec master..xp_myRand @r output
return @r
end

Steve

[oj]

kewl. your xp of yours better not leaks memory! :~)

--
-oj

"Steve Kass" <sk...@drew.edu> wrote in message

news:3CD19345...@drew.edu...

[Steve Kass]

Nothing is dripping from the CPU yet, but I'm keeping an eye on it.

Steve

[BP Margolin]

oj, Steve,

I pride myself on stealing only from the best ;-)

BPM

"Steve Kass" <sk...@drew.edu> wrote in message

news:3CD19345...@drew.edu...