create procedure dbo.get_normal_random_integer (@min int, @max int, @normality float = 1.0) as
/*
** INFO : Return a random number between @min and @max inclusive. A significant
** series of these numbers will produce a roughly normal (i.e. bell shaped)
** distribution centered between @min and @max.
**
** INFO : This procedure is not GUARANTEED to produce a statistically correct normal
** distribution, just a roughly normal distribution.
**
** INPUTS : @min = lower end of required range.
** @max = upper end of required range.
** @normality = probability curve shaping parameter, 1 means a normal normal,
** <1 means flatter,
** >1 means a tighter peak.
**
** NOTES :
*/
begin
declare @f float
declare @r int
declare @rand1 float
declare @rand2 float
-- scale normality.
set @normality = 3.5 * @normality
while (1=1)
begin
set @rand1 = rand()
set @rand2 = rand()
-- produce @f, mean 0, stdev 1
set @f = cos(2.0 * PI() * @rand1) * sqrt(-2.0 * log(1.0 - @rand2))
-- If the result is within the required range then exit this loop, otherwise try again.
if (@f >= -@normality and @f <= @normality) break
end
-- set range
set @f = (@max - @min + 1) / (@normality * 2) * @f
--offset to @min
set @f = ((@min + @max) / 2.0) + @f
-- round and return
set @r = round(@f, 0)
return @r
end
go