Generating Random Numbers in SQL Server 6.5 Stored Procedure 
Author Message
 Generating Random Numbers in SQL Server 6.5 Stored Procedure

Hello All:

Anyone familiar with generating random numbers in SQL Server 6.5  I have tried and I
always seem to get the same number or if I seed the RAND() function with an int value I
still get a random number that increment with the int value.  so this is no good for
creating a return sorted by random value.

When I use the RAND() by itself I get the following.
property_id randomvalue
------------------------------
36          0.2355724
53          0.2355724
54          0.2355724
55          0.2355724
57          0.2355724
58          0.2355724
59          0.2355724
60          0.2355724
61          0.2355724
62          0.2355724
63          0.2355724
64          0.2355724
67          0.2355724
68          0.2355724
69          0.2355724
70          0.2355724
71          0.2355724
72          0.2355724
73          0.2355724
74          0.2355724
76          0.2355724
77          0.2355724
78          0.2355724
79          0.2355724
80          0.2355724
81          0.2355724
82          0.2355724
83          0.2355724
84          0.2355724
85          0.2355724
86          0.2355724
88          0.2355724
89          0.2355724
91          0.2355724
92          0.2355724
94          0.2355724
95          0.2355724
96          0.2355724
97          0.2355724
129         0.2355724
130         0.2355724
131         0.2355724
136         0.2355724
137         0.2355724
139         0.2355724
140         0.2355724
141         0.2355724
143         0.2355724
144         0.2355724
145         0.2355724
146         0.2355724
152         0.2355724
159         0.2355724
164         0.2355724
165         0.2355724
166         0.2355724
167         0.2355724
168         0.2355724
169         0.2355724
170         0.2355724
171         0.2355724
172         0.2355724
173         0.2355724
181         0.2355724
183         0.2355724
185         0.2355724
186         0.2355724
188         0.2355724
189         0.2355724
190         0.2355724
193         0.2355724
195         0.2355724
196         0.2355724
197         0.2355724
198         0.2355724
199         0.2355724
200         0.2355724
201         0.2355724
202         0.2355724
203         0.2355724
204         0.2355724
205         0.2355724
206         0.2355724
208         0.2355724
209         0.2355724
210         0.2355724
211         0.2355724
212         0.2355724
215         0.2355724
216         0.2355724
217         0.2355724
218         0.2355724
219         0.2355724
220         0.2355724
221         0.2355724
222         0.2355724
223         0.2355724
224         0.2355724
225         0.2355724
226         0.2355724
227         0.2355724
228         0.2355724
229         0.2355724
230         0.2355724
231         0.2355724
232         0.2355724
233         0.2355724
234         0.2355724
236         0.2355724
243         0.2355724
244         0.2355724
245         0.2355724
246         0.2355724
247         0.2355724
248         0.2355724
249         0.2355724
(116 row(s) returned)

Any help would be appreciated, I need to generate truly random values.  I have tried just
about everything I can think of.

mike

  mike.vcf
1K Download


Wed, 10 Dec 2003 12:36:01 GMT
 Generating Random Numbers in SQL Server 6.5 Stored Procedure

Michael,

Quote:
> I need to generate truly random values.

No computer algorithm can generate "truly random values" ... the best that
can be hoped for is a sequence of pseudo-random values. For a set of "truly
random values", I would suggest attaching a machine to monitor radioactive
decay to your server, as that is one of the few processes which is believed
to be "truly random".

If you are willing to settle for a sequence of pseudo-random values, try
something like:

select rand (1000.0 * property_id * datepart(ms, current_timestamp))

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hello All:

> Anyone familiar with generating random numbers in SQL Server 6.5  I have
tried and I
> always seem to get the same number or if I seed the RAND() function with
an int value I
> still get a random number that increment with the int value.  so this is
no good for
> creating a return sorted by random value.

> When I use the RAND() by itself I get the following.
> property_id randomvalue
> ------------------------------
> 36          0.2355724
> 53          0.2355724
> 54          0.2355724
> 55          0.2355724
> 57          0.2355724
> 58          0.2355724
> 59          0.2355724
> 60          0.2355724
> 61          0.2355724
> 62          0.2355724
> 63          0.2355724
> 64          0.2355724
> 67          0.2355724
> 68          0.2355724
> 69          0.2355724
> 70          0.2355724
> 71          0.2355724
> 72          0.2355724
> 73          0.2355724
> 74          0.2355724
> 76          0.2355724
> 77          0.2355724
> 78          0.2355724
> 79          0.2355724
> 80          0.2355724
> 81          0.2355724
> 82          0.2355724
> 83          0.2355724
> 84          0.2355724
> 85          0.2355724
> 86          0.2355724
> 88          0.2355724
> 89          0.2355724
> 91          0.2355724
> 92          0.2355724
> 94          0.2355724
> 95          0.2355724
> 96          0.2355724
> 97          0.2355724
> 129         0.2355724
> 130         0.2355724
> 131         0.2355724
> 136         0.2355724
> 137         0.2355724
> 139         0.2355724
> 140         0.2355724
> 141         0.2355724
> 143         0.2355724
> 144         0.2355724
> 145         0.2355724
> 146         0.2355724
> 152         0.2355724
> 159         0.2355724
> 164         0.2355724
> 165         0.2355724
> 166         0.2355724
> 167         0.2355724
> 168         0.2355724
> 169         0.2355724
> 170         0.2355724
> 171         0.2355724
> 172         0.2355724
> 173         0.2355724
> 181         0.2355724
> 183         0.2355724
> 185         0.2355724
> 186         0.2355724
> 188         0.2355724
> 189         0.2355724
> 190         0.2355724
> 193         0.2355724
> 195         0.2355724
> 196         0.2355724
> 197         0.2355724
> 198         0.2355724
> 199         0.2355724
> 200         0.2355724
> 201         0.2355724
> 202         0.2355724
> 203         0.2355724
> 204         0.2355724
> 205         0.2355724
> 206         0.2355724
> 208         0.2355724
> 209         0.2355724
> 210         0.2355724
> 211         0.2355724
> 212         0.2355724
> 215         0.2355724
> 216         0.2355724
> 217         0.2355724
> 218         0.2355724
> 219         0.2355724
> 220         0.2355724
> 221         0.2355724
> 222         0.2355724
> 223         0.2355724
> 224         0.2355724
> 225         0.2355724
> 226         0.2355724
> 227         0.2355724
> 228         0.2355724
> 229         0.2355724
> 230         0.2355724
> 231         0.2355724
> 232         0.2355724
> 233         0.2355724
> 234         0.2355724
> 236         0.2355724
> 243         0.2355724
> 244         0.2355724
> 245         0.2355724
> 246         0.2355724
> 247         0.2355724
> 248         0.2355724
> 249         0.2355724
> (116 row(s) returned)

> Any help would be appreciated, I need to generate truly random values.  I
have tried just
> about everything I can think of.

> mike



Wed, 10 Dec 2003 14:45:49 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Stored Procedure not generating random values

2. SQL 7.0 Random function, does not work like SQL 6.5 Random function

3. EASY: SQL to generate a simple random number?

4. SQL to Generate Random Numbers?

5. PL/SQL Generating a random number

6. random number in a stored procedure

7. Random number between 10000000 and 99999999 in Stored procedure

8. SQL Server ODBC Driver Generates Error When Executing a Stored Procedure on SQL

9. sql 6.5 vs 7 - NULLS and auto generated numbers

10. MFC and SQL Server 6.5 Stored Procedures

11. 6.5 7.0 Upgrade SQL Server Performance Issues Stored Procedure

12. Debugging MS SQL Server 6.5 stored procedures from VB5.0


 
Powered by phpBB® Forum Software