declare @fieldForceCounter as int
declare @SaleDate as dateTime
declare @RandomNoSeed as decimal
set @fieldForceCounter = 1
set @SaleDate = '1 Jan 2009'
set @RandomNoSeed = 0.0
WHILE @fieldForceCounter <= 3
BEGIN
while @SaleDate <= '1 Dec 2009'
begin
INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) VALUES(@fieldForceCounter, @SaleDate, RAND(@RandomNoSeed))
set @saleDate = @saleDate + 1
set @RandomNoSeed = Rand(@RandomNoSeed) + 1
end
set @SaleDate = '1 Jan 2009'
set @fieldForceCounter = @fieldForceCounter + 1
END
GO
This T-SQL command was supposed to insert random values in the 'SaleValue'-column in the 'MonthlySales'-table.
But it is inserting '1' every time .
What can be the problem?
Two problems:
- Firstly, the rand() function returns a number between 0 and 1.
- Secondly, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number (which I suspect your algorithm above is trying to solve, by splitting it into multiple calls)
My favourite way around the second problem is to use a function that's guaranteed to return a unique value each time, like newid(), convert it to varbinary, and use it as the seed :)
Edit: after some testing, it seems you'll need to try using a different datatype for @RandomNoSeed; float behaves somewhat different to decimal, but still approaches a fixed value, so I'd recommend avoiding the use of @RandomNoSeed altogether, and simply use:
INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue)
VALUES(@fieldForceCounter, @SaleDate, RAND(convert(varbinary,newid())))
You have major issues here...
Decimal issues
The default precision/scale for decimal is 38,0. So you aren't storing any decimal part.
So you are only using RAND(0) for 1st iteration and RAND(1) for all subsequent iterations, which is 0.943597390424144 and 0.713591993212924
I can't recall how rounding/truncation applies, and I don't know what datatype SalesValue is, but rounding would give "1" every time.
Now, if you fix this and declare decimal correctly...
Seeding issues
RAND takes an integer seed. Seeding with 1.0001 or 1.3 or 1.999 gives the same value (0.713591993212924).
So, "Rand(1.713591993212924) + 1" = "RAND(1) + 1" = "1.713591993212924" for every subsequent iteration.
Back to square one...
To fix
- Get rid of @RandomNoSeed
- Either: Generate a random integer value using CHECKSUM(NEWID())
- Or: generate a random float value using RAND() * CHECKSUM(NEWID()) (Don't care about seed now)
Just a guess, but often rand functions generate a number from 0-1. Try multiplying your random number by 10.
