Rand() in User Defined 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

SQL Server Development (2000)

Rand() in User Defined Function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-07 : 11:26:16

Jamie writes "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"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 11:31:34

Is this a repeat post?
rand() won't work.
rand(seed) should but doesn't.
You can pass rand() in to the function but will have to be careful how you use it as you will need a separate call for each row (same as with seed really).
You can put the rand() in a view and set a variable from it in the function - but that can cause incorrect results as it is not doing what the optimiser expects.

Best not to use a function at all but do everything in an SP.

==========================================
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.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-07 : 13:33:55

[code]
create function dbo.udf_random_date ( @rand_day float,@rand_month float,@rand_year float )
returns smalldatetime
as
begin

declare @random_date smalldatetime

select @random_date =
CAST(
--Month
RIGHT(CAST(CAST(1+ @rand_month * 12 AS INT) as varCHAR(2)),2)
+ '/' +
--Day
RIGHT(CAST(CAST(1+ @rand_day * 28 AS INT) as varCHAR(2)),2)
+ '/' +
--Year
RIGHT(CAST(CAST(1997 + @rand_year * 8 AS INT) as varCHAR(4)),4)
as smalldatetime)
return @random_date
end
GO

select dbo.udf_random_date(RAND(CAST(NEWID() AS binary(4))),RAND(CAST(NEWID() AS binary(4))),RAND(CAST(NEWID() AS binary(4)))) random_date
[/code]

Prodev
Starting Member

8 Posts

Posted - 2004-05-07 : 16:34:54

Yes this is a repeat, I made the error or sending to the Ask SQL Team, and then actually joined the forum and posted direct as this appeared to be quicker. Thanks, sorry for the trouble.

Best Regards,

Jamie

James D. Beine - President
Prodev Studios

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources