0

I am referring to SQL Server 2012 Random string from a list

and would like to make it into a function. To the function, I pass the values to select from. Example Syntax:

RandomFrom('Bill','Steve','Jack',.....)

and it would randomly return one of them.

Community
  • 1
  • 1
Bill
  • 751
  • 2
  • 8
  • 18

2 Answers2

0

One way to solve the problem would be to create a User-Defined Table Type to contain the first names, and then pass that table to a Scalar-valued Function.

CREATE TYPE FirstNames AS TABLE (firstName varchar(50), RandomSeed varchar(128))
GO

DECLARE @myFirstNames as FirstNames
INSERT INTO @myFirstNames 
    SELECT 'Bill', NEWID()
    UNION SELECT 'Steve', NEWID()
    UNION SELECT 'Jack', NEWID()

SELECT * FROM @myFirstNames

Next, create a Scalar-valued Function that takes your new table type as a parameter.

CREATE FUNCTION RandomFirstName(@TableName dbo.FirstNames READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @name VARCHAR(50)

    SELECT TOP 1 @name = a.firstName 
    FROM @TableName a
    ORDER BY a.RandomSeed

    RETURN @name
END

Finally, call your function like so:

SELECT [dbo].[RandomFirstName] (@myFirstNames)
Jax
  • 111
  • 5
0

You can try something like this:

CREATE TABLE Contacts(id INT IDENTITY, name varchar(10))
GO

INSERT INTO dbo.Contacts VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
GO

CREATE VIEW vNames AS
SELECT * FROM (VALUES('Bill', NEWID()),('Steve',NEWID()),('Jack',NEWID())) Names(name, g)
GO

CREATE FUNCTION RandomFrom()
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @name VARCHAR(10)

    SELECT TOP 1 @name = name
    FROM dbo.vNames
    ORDER BY g

    RETURN @name
END
GO

UPDATE dbo.Contacts SET name = dbo.RandomFrom()

SELECT * FROM dbo.Contacts

Output:

id  name
1   Bill
2   Jack
3   Jack
4   Bill
5   Steve
6   Steve
7   Steve
8   Jack
9   Bill
10  Bill
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • Thanks, Can I do it in one shot? i.e. pass to functions the values to choose from. I would be selecting on value for each time the function is run. – Bill May 20 '15 at 15:40
  • Did you see jax's answer? – Giorgi Nakeuri May 20 '15 at 15:52
  • Yes, I did. What I need is abitlity to do Select RandomFrom('Bill','Steve','Jack','John') without having to create a type or table. – Bill May 20 '15 at 17:23