using rand() in SQL Server 1

[OhioSteve]

I wrote this in a custom function in SQL Server 2005:
declare @randomFloat float
set @randomFloat=rand()

I got this error message:
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.

How do I fix the problem??

 

[gmmastros]

There is a FAQ that exmplains why you can't do this. faq183-6047

As an alternative, you could pass the results of the Rand function in to a stored procedure.

Like this...

Alter Function GetRandomNumber
	(
	@MaxNumber Int,
	@RandomNumber Float
	)
Returns Int
As
Begin
	Return Convert(Int, @MaxNumber * @RandomNumber)
End

Call it like this...

[tt][blue]
Select dbo.GetRandomNumber(20, Rand())
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[OhioSteve]

I tried to read the FAQ you mentioned. I understood everything until the "seed value" section.

Your FAQ says that if you use the same seed each time, you get the same result each time. So why would anyone want to use a seed? Perhaps the answer is "because using a seed lets you put rand() into your function". But doesn't rand() become useless if it is not, well, random?

 

[gmmastros]

Well... Yeah... Random numbers that are not random are not, well... random.

What I was really referring to was the part where vongrunt talks about non-deterministic. Using the 'trick' I present in my previous post (where you pass the value of the Rand() function in to the function) does work. That is how you should proceed.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

what about this?

declare @randomFloat float
set @randomFloat=rand() * rand()
select @randomFloat


keep hitting F5

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Or this...

Select Rand()
Union All Select Rand()
Union All Select Rand()

Or

Select Rand(), Rand(), Rand()

Remarks
Repetitive invocations of RAND() in a single query will produce the same value.

I think it's odd that the first query (with the union), shows the same 3 numbers, but the 2nd query, with Rand() within the same select, generates 3 different numbers.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

at least newid is consistent

Select newid()
Union Select newid()
Union Select newid()


Select newid(), newid(), newid()


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

CREATE VIEW RandomNumber
AS
SELECT RandomNumber = rand()

GO

CREATE FUNCTION RandomFloat()
RETURNS float
AS
BEGIN
	RETURN (SELECT * FROM RandomNumber)
END
GO

select dbo.randomfloat(), dbo.randomfloat(), dbo.randomfloat()

select dbo.randomfloat()
union select dbo.randomfloat()
union select dbo.randomfloat()

 

[OhioSteve]

gmmastros:

I appreciate all of your help on this thread and the other thread. However, I don't want my programmers to have to add another argument. I want it to be easy for them to use my function.

ESquared:

Clearly, you have wisdom of an ancient jedi sage. You inspired me to create my solution below, and I am giving you a star now.

everyone:

My solution was to create a view (called randomNumber) with the syntax "SELECT RAND() AS rn".

Then I used it in this code fragment~
Select @randomFloat = rn From dbo.randomNumber
set @randomFloat = @randomFloat * @maxInt
set @randomInt = convert(int,@randomFloat)
set @randomInt = @randomInt + @minInt

My goal was to create a function that returns a random number between minInt and maxInt. For example if you called it with 1 and 6, it would behave just like a die roll. I'm pretty sure that those four lines of code achieve that. If I am mistaken please let me know.