How to pick random numbers between two numbers with T-SQL

Below is a T-SQL example that will pick a random number between 1 and 50.

SELECT CAST(RAND() * (51 - 1) + 1 AS INT) AS Random#

That’s a bit boring though.
What about parameters?
What about a use case?
Where’s the familiar glamour of coding with T-SQL?

I hear ya.

Below is a T-SQL example that could make you a multimillionaire!

This T-SQL code will pick random numbers for the Euromillions lottery.

Good luck.

DECLARE @a INT;
DECLARE @b INT;
DECLARE @count INT;
DECLARE @pick INT;

DROP TABLE IF EXISTS #Num;

CREATE TABLE #Num (
	Number INT
	,NumberType VARCHAR(255)
	);

SET @a = 1
SET @b = 51
SET @count = 1

WHILE @count < 6
BEGIN
	SET @pick = CAST(RAND() * (@b - @a) + @a AS INT)

	IF (
			SELECT Number
			FROM #Num
			WHERE Number = @pick
			AND NumberType = 'Main'
			) IS NULL
	BEGIN
		INSERT INTO #Num (
			Number
			,NumberType
			)
		SELECT @pick
			,'Main';

		SET @count = @count + 1
	END
END

SET @a = 1
SET @b = 13
SET @count = 1

WHILE @count < 3
BEGIN
	SET @pick = CAST(RAND() * (@b - @a) + @a AS INT)

	IF (
			SELECT Number
			FROM #Num
			WHERE Number = @pick
			AND NumberType = 'Lucky'
			) IS NULL
	BEGIN
		INSERT INTO #Num (
			Number
			,NumberType
			)
		SELECT @pick
			,'Lucky';

		SET @count = @count + 1
	END
END

SELECT Number
	,NumberType
FROM #Num
ORDER BY NumberType DESC
,Number ASC;

If you found this post helpful please like/share/subscribe.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s