Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

T-SQL Randon Number Generator


 / Published in: SQL
 

  1. -- Setup some vars we'll need
  2. DECLARE @prng TABLE (seed BIGINT, rnum nchar(10))
  3. DECLARE @seeds TABLE (seed BIGINT)
  4. DECLARE @seed BIGINT
  5. DECLARE @C1 BIGINT, @C2 BIGINT, @C3 BIGINT
  6. SET @seed = 0
  7. SET @C1 = 1664525
  8. SET @C2 = 4294967296
  9. SET @C3 = 1013904223
  10.  
  11. -- Create a seed table so we can have some data to use
  12. WHILE @seed < 10
  13. BEGIN
  14. INSERT INTO @seeds (seed) VALUES (@seed)
  15. SET @seed = @seed + 1
  16. END
  17.  
  18. -- Create our PRNG (inserts into table for illustrative purposes)
  19. -- prng(seed) ::= ((((C1 * seed) % C2) + C3) % C2) / C2
  20. -- Then convert prng(seed) into a string
  21. -- of 10 chars, 8 of which are decimal places
  22. INSERT INTO @prng
  23. SELECT
  24. seed,
  25. REPLACE(
  26. STR(
  27. ( CAST((((@C1*seed)%@C2)+@C3)%@C2 AS FLOAT) )
  28. / ( CAST(@C2 AS FLOAT)),
  29. 10, 8
  30. ),
  31. ' ', '0') AS rnum
  32. FROM @seeds
  33.  
  34. -- Let's take a look at what we created
  35. SELECT * FROM @prng

Report this snippet  

You need to login to post a comment.