Generate a Random number between 10,000 and 99,999

[VBCrazyCoder]

Hi all,

Just wondering if anyone had an idea on how to generate a random number between the range of 10,000 and 99,999 (Sql Server 2000). I think I can make use of Rand() but I don't think that this would be a different number everytime time it was called and not sure how to give it min of 10,000 and max of 99,999. I would assume that this is pretty common and so there must be something built in?

Any ideas?

[dannymking]

You can try

Dim myVal As long
  Randomize
  Do While MyVal < 10000
    MyVal = Rnd * 100000
  Loop

[Hack]

Private Sub Command1_Click()
Dim MyNumber As Long
Randomize
MyNumber = (99999 * Rnd) + 10000
Text1.Text = MyNumber
End Sub

[szlamany]

If you are looking for a T-SQL solution - here's some info...

We like to get the same random order from one run to the next - so we seed the RAND() function at the start of our STORED PROCEDURE...

DECLARE @RD REAL
SET @RD=RAND(117)

We chose 117 for no particular reason - but it's a steady consistent seed, so that the random order from that point forward will be consistent. I'm guessing that if you do not seed it will be a different random order from one run to the next - but you will have to check that.

Then, in a LOOP in the SPROC we keep calling...

SET @RD=RAND()

Each time we call for RAND() we get a different value.

BOOKS ONLINE says that RAND() will return a value from 0 through 1 - but I'm guessing it either never returns 0 or never returns 1 - one or the other.

So with that said...

SELECT (@RD * 89999) + 10000

Should do what you want.

Big problems with RAND() though - if you use it in an UPDATE statement for lots of records, they all get the same RAND().

If you use it in INSERT INTO .... SELECT ... RAND() ...

they all get the same RAND().

You kind of have to do the work in a table variable in the stored procedure - in a loop - that's worked well for us.

Good luck!

[VBCrazyCoder]

For the T-SQL solution, we wanted to put this into a function so we can use it all over as needed, but apparently you cannot call Rand() in a function (UDF) because it is non-deterministic. I am curious as to how you implemented it?

[szlamany]

For the T-SQL solution, we wanted to put this into a function so we can use it all over as needed, but apparently you cannot call Rand() in a function (UDF) because it is non-deterministic. I am curious as to how you implemented it?

Yep - you are right - cannot use it in a FUNCTION - we went down that path as well!

It was quite a fight...

We used it in a SPROC - here's a snippet from the stored procedure.

Set @RD=Rand(117)
Set @RI=(Select Min(StuId) From Algorithm_T Where Yr=@CrsYr and Bldg=@Bldg and RandomOrder is null)
 
While @RI is not Null
Begin
    Update Algorithm_T Set RandomOrder=Rand() Where Yr=@CrsYr and Bldg=@Bldg and StuId=@RI
    Set @RI=(Select Min(StuId) From Algorithm_T Where Yr=@CrsYr and Bldg=@Bldg and RandomOrder is null)
End

Basically we decided to actually STORE the random figure in a table - since we kept getting burned with all these restrictions on the RAND() function.

We created a table - put a REAL column in it for the value.

And you can see we are looping through the table filling in the RANDOMORDER column - if it's NULL we fill it in, and then get the next kid.

[szlamany]

BTW - there are tricks to getting around the non-deterministic problem.

Put it in a VIEW - have the FUNCTION look at the VIEW??

[VBCrazyCoder]

I went the function / view route - works great! Thanks for the help.