Unique Random Number Generation 1

[anik21]

I am trying to generate unique random number between 1000 and 1000,000,000,000 (trillion) to insert into a unique field in the SQL database . How do I achieve this?
code in VBScript or a stored procedure will do.

My current code in the stored proc is as follows:



Declare @Random bigint;
DECLARE @Upper bigint;
DECLARE @Lower bigint

-- This will create a random number between 1 and 1000,000,000
SET @Lower = 1 -- The lowest random number
SET @Upper = 1000000000 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

select RandomNumber = @Random


could someone please help me with the code ? Do I have to check each generated random number before inserting into the table to see if it already exists in the table?

Thanks.

 

[PHV]

As this RandomNumber must be unique, I guess you've created a unique index on this column, so it's easy to amend your stored procedure to do the check (inside a loop)

Hope This Help
PH.

 

[markdmac]

How about this?

Randomize
MyNumber = Rnd()
MyNumber = Int(MyNumber * 1000000)

 

[loganginger]

Just to add to markdmac's code:

Randomize
MyNumber = Int(rnd() * 1000000 + 1)

Because we're using the int() function you will only get a number between 0 and 999,999.

 

[markdmac]

Good catch loganginger. Thanks.

 

[PHV]

Anyway, you have to check (select) the unicity of the so generated number.

 

[anik21]

Does this generate a unique random number between

1000 and 1 trillion ?

You are multiplying the random number by 1000000, what are the odds that it does not generate the same number again..

MyNumber = Rnd()

 

[anik21]

You mean my stored procedure works, but I need to check for uniqueness ... Is it so PHV?

Everytime , I generate a random number I need to check in the stored procedure, if it is already generated(in other words, if it is in the database). Could you help me with the SP...


Declare @Random bigint;
DECLARE @Upper bigint;
DECLARE @Lower bigint

-- This will create a random number between 1 and 1000,000,000
SET @Lower = 1000 -- The lowest random number
SET @Upper = 1000000000000 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)


If @Random = (Select Distinct(Survey_ID) from Results where Survey_ID = @Random)

Begin
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

End

Else
Begin
SELECT RandomNumber = @Random
End



With this, I can only check once. what if the random number generated in the if case is already there in the DB. Then there would be a duplicate again.I am thinking of having a while loop. Could you please help on this.

 

[PHV]

In your SP language can you construct loops ?
Perhaps it's better to post in a forum related to your DB.

 

[mrmovie]

i agree with PHV, you should ask this q in a DB forum.
whilst the posts about Rnd() are correct and you will prob need to use them at some point you have to check for unicity, (PHV, are you sure that is a word?;-)), and as PHV suggested the DB side will take care of that,,,unless you want to search the DB first for the RND() number you have generated,,,which i doubt very much

regards
rich