Error: Invalid use of 'rand' within a function.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error: Invalid use of 'rand' within a function.

Author

Topic

Prodev
Starting Member

8 Posts

Posted - 2004-05-05 : 13:47:05

Hello,

I am new to your forum, what a great opportunity this is.

I require assistance with the following...

"I am working to create a date value randomizer to generate random dates for building test data.

This SPROC Returns a Random date mm-dd-yyyy

---------------------------------
CREATE PROCEDURE dbo.GetRandomDate
AS
DECLARE @rndDate NVARCHAR(10)
-- Create the variables for the random date generation
DECLARE @rndYear int
DECLARE @rndMonth int
DECLARE @rndDay int

DECLARE @HighestYear int
DECLARE @LowestYear int

DECLARE @HighestMonth int
DECLARE @LowestMonth int

DECLARE @HighestDay int
DECLARE @LowestDay int

SET @LowestYear = 1997 -- The lowest year
SET @HighestYear = 2004 -- The highest year

SET @LowestMonth = 1 -- The lowest month
SET @HighestMonth = 12 -- The highest month

SET @LowestDay = 1 -- The lowest day
SET @HighestDay = 28 -- The highest day

SELECT @rndYear = Round(((@HighestYear - @LowestYear -1) * Rand() + @LowestYear), 0)
SELECT @rndMonth = Round(((@HighestMonth - @LowestMonth -1) * Rand() + @LowestMonth), 0)
SELECT @rndDay = Round(((@HighestDay - @LowestDay -1) * Rand() + @LowestDay), 0)

--

Set @rndDate = (RTRIM(CAST(@rndMonth as CHAR(2))) + '-' + RTRIM(CAST(@rndDay as CHAR(2))) + '-'
+ CAST(@rndYear as CHAR(4)))

SELECT @rndDate
----------------------------

I want to convert this to a User Defined Function but SQL Server returns an error:

Error: Invalid use of 'rand' within a function.

rand() without seed is nondeterministic, and nondeterministic functions are not permitted in UDF's, do I need to seed rand() like..
rand(10) ???

or is there some other trick?

TIA,

Jamie

James D. Beine - President
Prodev Studios

Blog: ProdevStudios.com/blog
Internet: ProdevStudios.com
Email: beinejd@prodevstudios.com
Toll Free: 800-577-0482
Direct: 270-444-0073
Fax: 270-444-6525"

Prodev
Starting Member

8 Posts

Posted - 2004-05-05 : 14:44:27

[!] Update I have posted my attempt at a user Defined Function using rand() that produces this error on my blog at http://www.prodevstudios.com/blog -JB.

Best Regards,

Jamie

James D. Beine - President
Prodev Studios

Blog: ProdevStudios.com
/blog
Internet: ProdevStudios.com
Email: beinejd@prodevstudios.com
Toll Free: 800-577-0482
Direct: 270-444-0073
Fax: 270-444-6525

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 14:54:49

Yes. UDF's must be deterministic and without a seed rand is not deterministic as the error message says.
Basically given the same input a udf should always return the same output.
Don't think it would be very useful for generating random data.

When I build something like this I usually look at each table and include in a script code to populate it.
Things like your upper and lower bounds I will hold in a table which are set at the beginning of the build run (together with the amount of data to populate with.

Would also not tend to use random data but would have it skewed so that there are a lot of some values and few of some others.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Prodev
Starting Member

8 Posts

Posted - 2004-05-05 : 15:24:05

The Stored Procedure works, I want a Function because I need to generate Millions of lines of test data that would simulate years of data on a complex system. - JB.

So I understand this so far.

No sprocs from within a function. Only ESP's and Functions.

But why isn't rand() working. I even tried seeding rand(10); No luck.

See function code posted on my blog.

Best Regards,

Jamie

James D. Beine - President
Prodev Studios

Blog: ProdevStudios.com
/blog
Internet: ProdevStudios.com
Email: beinejd@prodevstudios.com
Toll Free: 800-577-0482
Direct: 270-444-0073
Fax: 270-444-6525

Prodev
Starting Member

8 Posts

Posted - 2004-05-05 : 16:52:11

SOURCE: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_460j.asp

The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:

@@CONNECTIONS @@PACK_SENT GETDATE
@@CPU_BUSY @@PACKET_ERRORS GetUTCDate
@@IDLE @@TIMETICKS NEWID
@@IO_BUSY @@TOTAL_ERRORS RAND
@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
@@PACK_RECEIVED @@TOTAL_WRITE

Bummer!

Best Regards,

Jamie

James D. Beine - President
Prodev Studios

Blog: ProdevStudios.com
/blog
Internet: ProdevStudios.com
Email: beinejd@prodevstudios.com
Toll Free: 800-577-0482
Direct: 270-444-0073
Fax: 270-444-6525

Prodev
Starting Member

8 Posts

Posted - 2004-05-05 : 16:57:09

Bryn Waibel Posted to Channel-9 a solution to my problem,

http://channel9.msdn.com/ShowPost.aspx?PostID=6329#6329

JB

You can see the solution SQL on my blog.

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-05 : 16:57:43

Jamie
The thing is, because Rand doesnt return the same value each time it is called, it cannot be used within a function.

One way around this is to pass in your random number as a parameter

.... I wouldnt want to try generating 3 random numbers in the call to the function, so I would be inclined to change the function to produce a date as <your random number> of days after the base date (with suitable coding to allow for when the day of the month is after the 28th as your original code seems to disallow this.

Does this help you ?

Graham

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 01:52:51

Functions tend to slow things down.
I wouldn't try generating millions of rows with one. Use an SP instead.

rand(seed) should work as it is deterministic
But as you can see from
create function fff
()
returns int
as
begin
return rand(10)
end
go

it's not allowed.
A way round this is to put the rand in a view
create view getrand
as
select r = rand()
go

drop function fff
go
create function fff
()
returns int
as
begin
return (select 1000 * r from getrand)
end
go

select dbo.fff()

But be careful. This is fooling the optimiser into doing something that is invalid. If it gets back a single row then it should be ok but if it generates a query plan whichs relies on the function being deterministic it can give incorrect results. In particular don't try to include it aggregate queries.
But anyway I think you will have problems trying to implement this using a function even if it works.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Prodev
Starting Member

8 Posts

Posted - 2004-05-06 : 09:16:59

Bryn Waibel of Microsoft presented a fairly adequate solution. Which I posted about on my blog.
I have yet to run the MM Row process (scheduled for sometime today) I will post on perfs, etc on my blog. So we can see the diff between use of the function and using the stored proc for this. I am guessing your experience with Functions (being greater than my own) will prove correct and that the Function will prove less that satisfactory with respect to performance.

Thanks for your input. -JB

Best Regards,

Jamie

James D. Beine - President
Prodev Studios

Blog: ProdevStudios.com
/blog
Internet: ProdevStudios.com
Email: beinejd@prodevstudios.com
Toll Free: 800-577-0482
Direct: 270-444-0073
Fax: 270-444-6525

Prodev
Starting Member

8 Posts

Posted - 2004-05-06 : 10:26:56

Can anyone point me at a good SQL Server perormance guide/standard? How would you go about benchmarking SQL Server processes (other than just arbitrarily watching perf monitors, and saying , well at least its working) ? Should I just setup my own baseline and track progression, changes against that baseline?

Thanks,

Jamie

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 13:25:55

Be interested to know what that was.
My post above about the view is the only way I know to get a random number out of a function - but see my warning about it. I'd be surprised if anyone from Microsoft suggested it.
The only other thing you can do is pass the random number in to the function but then mostly you can't use it in set operations and get different results per row which rather negates the use of it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources