CODEWITHSUNDEEP
sql-serverselectrandom
rjbogz
rjbogz

Reputation: 870

selecting random data from a predefined list

I have a list of employee ids, lets say:

Q1654
F2597
Y9405
B6735
D8732
C4893
I9732
L1060
H6720

These values are not in any one of my tables, but I want to create a function that will take in no parameters and return a random value from this list. How can I do this?

Upvotes: 2

Views: 4776

Answers (1)

xQbert
xQbert

Reputation: 35343

Without getting into random number theory, here's one method:

http://sqlfiddle.com/#!6/192f2/1/0

it basically uses the function newID to generate a random value then sorts by it returning the top 1 record.

Given that it needs to be in a function and function's can't use newID... interjecting a view in the middle eliminates the problem.

Select * into myRand FROM (
SELECT 'Q1654' as val UNION
SELECT 'F2597'UNION
SELECT 'Y9405'UNION
SELECT 'B6735'UNION
SELECT 'D8732'UNION
SELECT 'C4893'UNION
SELECT 'I9732'UNION
SELECT 'L1060'UNION
SELECT 'H6720') b;


Create View vMyRand as 
Select top 1 val from myRand order by NewID();

CREATE FUNCTION GetMyRand ()
RETURNS varchar(5)

--WITH EXECUTE AS CALLER
AS
BEGIN
Declare @RetValue varchar(5)
--@configVar =
Select @RetValue = val from vmyRand
RETURN(@retValue)
END;

Upvotes: 2

Related Questions