SQL Generate A Random Number, Select Random Number

The following code example shows how to generate a random number between 1 and 500.

— DECLARE VARIABLES
DECLARE @RandomNumber        float
DECLARE @RandomNumberOutput    int
DECLARE @MaxNumber            int
DECLARE @MinNumber            int

— SET THEMINIMUM POSSIBLE NUMBER
set @MinNumber = 1

— SET THE MAXIMUM POSSIBLE NUMBER
set @MaxNumber = 500

— GENERATE THE RANDOM VALUE
set @RandomNumber = RAND()
set @RandomNumberOutput = ((@MaxNumber + 1) – @MinNumber) * @RandomNumber + @MinNumber

print @RandomNumberOutput

Change the @MinNumber and set @MaxNumber to alter the possible outputs

The following piece of code shows how to update a field in a table with a random number.
In this example I am updating a field called RANDOM_NUMBER in a table called TABLE.

— DECLARE VARIABLE TO HOLD THE URN OF THE RECORD TO UPDATE
DECLARE @URN varchar(50)

— INTITATE WHILE LOOP TO RUN THROUGH THE TABLE INSERTING A RANDOM NUMBER
— INTO A FIELD CALLED RANDOM_NUMBER
while (select count(*) from TABLE where RANDOM_NUMBER = ”) > (0)
BEGIN

— SELECT THE URN OF THE FIRST RECORD TO UPDATE
select top 1 @URN = urn from TABLE where RANDOM_NUMBER = ”

— GEBERATE A RANDOM NUMBER
SELECT @RandomNumber = RAND()
SELECT @RandomNum = ((@MaxNumber + 1) – @MinNumber) * @RandomNumber + @MinNumber

— UPDATE THE TABLE WITH THE RANDOM NUMBER
update TABLE
set RANDOM_NUMBER = rtrim(cast(@RandomNum as char))
where ref = @URN

END

Advertisements