Wednesday, April 11, 2012

Pick One Random Number from a Set of Numbers

A while back I posted a means to generate random numbers in SQL. I ran across a situation where I need to pick one number randomly from a set of numbers.

I found a decent solution here, but I didn't want to create a separate view and scalar function for this task.

Instead I create a table variable with a single integer column as the primary key. Next, I insert each of the values of the small set of numbers into the table variable. Finally, I perform a SELECT against the table variable with NEWID() specified in the ORDER BY clause, so that a randomly-chosen member of the set will be returned. 

        DECLARE @Code int

        DECLARE @Codes TABLE
            Code int PRIMARY KEY
        INSERT INTO @Codes (Code) VALUES (3)  
        INSERT INTO @Codes (Code) VALUES (6)
        INSERT INTO @Codes (Code) VALUES (9)
        INSERT INTO @Codes (Code) VALUES (12)

        IF @Code IS NULL
            SELECT TOP 1 @Code = Code FROM @Codes ORDER BY NEWID()

In my case this is to provide a stored procedure, which may or may not have a value provided for the @Code input parameter, be able to automatically pick a random value in case @Code is NULL. The table variable isn't necessary if the value being picked is among the primary key values of an existing table; a random selection can be obtained simply by doing a SELECT with NEWID().