Question:
I’m doing a select statement within a stored procedure, which returns several columns. I would like to assign a random number to each column, but when I use the following statement, the random number gets applied to each column. How can I do this?
Select MyTable.UniqueId, rand(10) as RandNum from MyTable where MyTable.UniqueId in(1,2,3,4,5,6)
I get:
1 .125
2 .125
3 .125
4 .125
I want:
1 .1532
2 .3325
3 .2525
4 .6626
Answer:
The problem is twofold. First of all, rand(10) will always return the same number. You have to either not provide a seed, or come up with a way of picking a “random” seed. You can use parts of the date.
Second, the select is one statement. The rand function gets executed only once, just like the getdate function doesn’t return different values. You would need to use a cursor to traverse the table and use the rand function on each row.
