RANDOM positive integer number

Question

RANDOM positive integer number

ArthurC

SSCommitted

Points: 1685
More actions
September 19, 2003 at 9:26 am

#101675

Is there anyone knows how to create a function which returns a random positive integer number within user defined range? I tried different ways of approach but not quite successful. I know that SQL Server has a build-in function named RAND but it is returning a random float value from 0 through 1 only, not what I am looking for. Anyone could help?
AC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You will find that you cannot use the RAND function within a UDF since it is non-deterministic.

As a work around you could create a view which returns a random number and then select the value from the view within your UDF.

RAND will only return a float value, try multiplying it by a larger number and using cast or convert to change it to an int.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi AC,

quote:
Is there anyone knows how to create a function which returns a random positive integer number within user defined range? I tried different ways of approach but not quite successful. I know that SQL Server has a build-in function named RAND but it is returning a random float value from 0 through 1 only, not what I am looking for. Anyone could help?

I don't think you can do this within a function, because as Paul said the output is nondeterministic, meaning same input not always same output.

However, here is something I use

SELECT CONVERT(INT, 1000 * RAND() + 1)

where 1000 is my upper bound

HTH

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

trabun SSC Enthusiast Points: 134 More actions · Answer 3

I had the same need and tried damn near everything...but found a solution that works.

paulhumphris was on the track that worked for me.

First, I created a view:

CREATE VIEW RANDOM_ASSIST



AS 

    SELECT RAND() as [RAND]

Then, a function:





CREATE  FUNCTION DBO.RANDOM_INTEGER (



    @MINIMUM_INTEGER int = 0   -- lowest number ya want

   ,@MAXIMUM_INTEGER int = 100 -- highest number ya want

)   RETURNS INT



AS BEGIN



    DECLARE @RETVALINT

    DECLARE @RAND FLOAT -- the random number

  

    SELECT @RAND = [RAND] From DBO.RANDOM_ASSIST



    RETURN ROUND(@MINIMUM_INTEGER + (@RAND * (@MAXIMUM_INTEGER - @MINIMUM_INTEGER)), 0) 

END

Sorry, I know someone will gripe. I HATE camel notation like tblTableName or fn_functionName. I prefer the CAPSLOCK for functions.

Cheers

It is a privilege to see so much confusion. -- Marianne Moore, The Steeplejack