code_junkie

Generate Random values from SQL

Nov 12th, 2011
61
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.99 KB | None | 0 0
  1. DECLARE @SaltCount INT;
  2. SELECT @SaltCount = COUNT(*) FROM tmp_NewLogin;
  3. PRINT 'Set Salt values for all records' + CAST(@SaltCount AS VARCHAR(10))
  4. DECLARE @CharPool CHAR(83);
  5. DECLARE @Salt VARCHAR(10);
  6. SET @CharPool = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!"#$%&()*+,-./:;<=>?@';
  7.  
  8. SET NOCOUNT ON;
  9. updateSaltValue:
  10. SET @Salt = ''
  11. SELECT @Salt = @Salt + SUBSTRING(@CharPool, number, 1) FROM
  12. (
  13. SELECT TOP 10 number FROM MASTER..[spt_values] WHERE TYPE = 'p' AND Number BETWEEN 1 AND 83
  14. ORDER BY NEWID()
  15. ) AS t
  16.  
  17. UPDATE TOP(1) [table] SET [Salt] = @Salt WHERE [Salt] IS NULL
  18.  
  19. IF (@@ROWCOUNT > 0)
  20. GOTO updateSaltValue
  21.  
  22. SET NOCOUNT OFF;
  23.  
  24. PRINT 'Completed setting salts for all records';
  25.  
  26. create view [dbo].[wrapped_rand_view]
  27. as
  28. select rand( ) as random_value
  29.  
  30. create function [dbo].[wrapped_rand]()
  31. returns float
  32. as
  33. begin
  34. declare @f float
  35. set @f = (select random_value from wrapped_rand_view)
  36. return @f
  37.  
  38. select substring(replace(newid(),'-',''),0,10)
Add Comment
Please, Sign In to add comment