Generating random numbers is way too important to leave to chance!

Hence I refer you to the second link in my signature articles. 😀

In that article, you will find a FUNCTION called RN_MULTINOMIAL(@Binomial, URN), which along with the TYPE declaration for Distribution, you should first run and then try this:

-- Data definition and setup

DECLARE @NumberOfRNs INT

SELECT @NumberOfRNs = 100

--CREATE TYPE Distribution AS TABLE (EventID INT, EventProb FLOAT, CumProb FLOAT)

DECLARE @Binomial AS Distribution

-- Simulate a coin toss with a Binomial Distribution

INSERT INTO @Binomial

SELECT 0, 0.5, 0.5 UNION ALL SELECT 1, 0.5, 1.0

-- Create random numbers for the selected distributions

SELECT TOP (@NumberOfRNs)

RandomBinomial = dbo.RN_MULTINOMIAL(@Binomial, URN)

INTO #MyRandomNumbers

FROM sys.all_columns a1 CROSS APPLY sys.all_columns a2

CROSS APPLY (SELECT RAND(CHECKSUM(NEWID()))) URN(URN)

SELECT RandomBinomial, Number=COUNT(RandomBinomial)

FROM #MyRandomNumbers

GROUP BY RandomBinomial

DROP TABLE #MyRandomNumbers

Unless I've totally missed what you want, you're trying to generate random events (like a coin toss) according to a binomial distribution.

Is that correct?

Edit: Ooops! I did misinterpret slightly. But the same FUNCTION can be used to generate a multinomial distribution. Your events then are all the integers between MIN and MAX. So simply populate these into the rows of the @Binomial distribution table (call it @Multinomial if you'd rather be precise) with equivalent probabilities for each row. Then let'er rip!

To make that a bit more clear, populate the @Binomial table something like this (the 3rd column is the cumulative probability):

--CREATE TYPE Distribution AS TABLE (EventID INT, EventProb FLOAT, CumProb FLOAT)

DECLARE @Binomial AS Distribution

DECLARE @MinNumber INT = 50, @MaxNumber INT = 500

;WITH Tally AS (

SELECT n=number

FROM [master].dbo.spt_values Tally

WHERE [Type] = 'P' AND Number BETWEEN @MinNumber AND @MaxNumber)

INSERT INTO @Binomial

SELECT n, 1./(1+@MaxNumber-@MinNumber), (1.+n-@MinNumber)/(1.+@MaxNumber-@MinNumber)

FROM Tally

SELECT *

FROM @Binomial

I've also included the CREATE TYPE statement you'll need to run before you CREATE the FUNCTION because a quick review of the article makes it seem kind of hidden.