Creating a gaussian random number in SQL Server 2005

I've been looking into this for a while, but so far I couldn't find anyone having posted a working sample of Gaussian random number generator ( and I need it for my probject)

As a bonus I also show how to modify the box-muller algorithm so that the resulting random numbers will conform to a given mean and standard deviation. It's very simple although it wasn't as intuitive as I wished, personally.



-------------------------CODE BELOW---------------------------

-- =============================================
-- Author:  Alwyn Aswin
-- Create date: 01/02/2009
-- Description: Generate a normally distributed random number.
-- NOTE: Please leave the author's attribution, if you copy this code. 
-- =============================================
CREATE PROCEDURE BoxMullerRandom
  @Mean float = 0
 ,@StdDev float = 1
 ,@BMRand float out
AS
BEGIN
 --@choice is the variable used to store the random number to return
 declare @choice float, @store float, @choiceid uniqueidentifier
 
 --checks to see if a box muller random number was already cached from previous call.
 select top 1 @choiceid = randomid, @choice = random from boxmullercache
 if(@choice is not null) -- if we do, delete that entry, since it's useable only once.
 begin
  print 'loading from cache'
  delete from boxmullercache
  where randomid = @choiceid
 end
 else --otherwise, generate a pair of box muller random number.
 begin
  print 'generate new ones'
  declare @MethodChoiceRand float
  set @MethodChoiceRand = rand()

  --We re-roll if we get a 0, and use 0.5 as the cutoff point.
  while @MethodChoiceRand = 0
  begin
   set @MethodChoiceRand= rand()
  end 

 -- Reroll if @MethodChoiceRand = 0, this will ensure that the interval, may be divided into 2 groups with equal number of members.
 -- AND it has the advantage of removing the problematic ln(0) error from the Box-Muller equation. 
  declare @rand1 float, @rand2 float
  select @rand1 = rand(), @rand2 = rand()
  while @rand1 = 0 or @rand2 = 0
  begin
   select @rand1 = rand(), @rand2 = rand()
  end

  declare @normalRand1 float, @normalRand2 float
  SELECT @normalRand1 = sqrt(-2 * log(@rand1)) * cos(2*pi()*@rand2)
     ,@normalRand2 = sqrt(-2 * log(@rand1)) * sin(2*pi()*@rand2)

  print 'box muller no 1:' + convert(varchar,@normalRand1) + ', box muller no 2:' + convert(varchar,@normalRand2)
  --RandomlySelects which one to store, which one to save.
  
  if @MethodChoiceRand <= 0.5
  begin
   print 'choice 1'
   select @choice = @normalRand1, @store = @normalRand2  
  end
  else if @MethodChoiceRand > 0.5
  begin
   print 'choice 2'
   select @choice = @normalRand2, @store = @normalRand1
  end
  
  --stores the other pair into the cache to be retrieved during subsequent call to this method.
  insert into boxmullercache (randomid, random)
  values (newid(),@store)
 end
 
 --fix up the random number, so that it should have the correct mean and standard deviation.
 set @BMRand = @choice * @stddev + @mean
END
GO

------------------------------------------------------------

I leave out the creation of the cachetable to the reader. You need to create a table to hold the other of the 2 values created via the Box-Muller algorithm! It should be fairly straight forward, it just needs an ID and a float column, which can be deduced from the code above.

Please feel free to comment or suggest ways on improving the code.