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().
