Random Number Generator getting Asterisks 3

[teblack]

Below is what I'm using to generate a random number based on a new input record. It works fine with the expecetion of once in a while it will generate an asterisks for the field, which is wrong. Can anyone tell me why it's doing that.

DECLARE @max int
DECLARE @sublocation char(3)
SET @max = 999
SET @sublocation = convert(int, rand()* 100000) % @max + 100
select @sublocation
[/copde]

I need the number to be a three digit number betweeen 001 - 999

Thanks in advance for the help with this.

Thanks again,


TBlack -

 

[vongrunt]

Because of +100 offset, this code generates numbers in range 100 - 1099. So when random number hits 4 digits, you get format overflow character instead (*).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

Try this...

SET @sublocation = convert(int, rand() * @Max) + 1



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[teblack]

Thanks, both changes, fixed the problem...

TBlack -

 

[JamesLean]

In general, to generate a random number between @min and @max (inclusive) you can use:

SELECT CAST(RAND() * (@max - @min + 1) AS int) + @min

--James