Return a random row number using a function in SQL Server

Question

I'm trying to write a function that selects a set of rows from a table and returns one of these randomly. The first steps of the function create and fill a local table variable holding the suitable row IDs. The table is defined as such:

DECLARE @SuitableID TABLE (
  Id INT
)

This table can hold any number of IDs. The function needs to return one of these randomly, but I can't find out how to do it. I've tried the following:

DECLARE @ReturnedID INT
SELECT TOP 1 @ReturnedID = Id
FROM @SuitableID
ORDER BY NEWID()

Which returns an error:

Invalid use of a side-effecting operator 'newid' within a function.

TAMPLESAMPLE also doesn't seem to work:

SELECT @ReturnedID = Id
FROM @SuitableID
TABLESAMPLE (1 ROWS)

I think this syntax is correct, but my database disagrees.

Incorrect syntax near the keyword 'TABLESAMPLE'.

What am I doing wrong, and how can I perform the operation I want?

Answer 1

For this and similar cases, I usually have a special view in the database:

create view [dbo].[sys_ViewNDF]
as
select rand() as [Rnd], getdate() as [Now], getutcdate() as [UTCNow], newid() as [GUID];

You can then join it with your table inside your UDF, like this:

SELECT TOP (1) @ReturnedID = s.Id
FROM @SuitableID s
  cross join dbo.sys_ViewNDF v
ORDER BY v.GUID;

Answer 2

    select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

This selects a random number so i dont know if you can incorporate this in

Answer 3

Can this be usefull to you? At least it works :)

CREATE VIEW dbo.GetNewID
AS
SELECT NewId() AS [NewID]
GO


CREATE FUNCTION GetRandomID()
RETURNS INT
AS
BEGIN

DECLARE @SuitableID TABLE (
Id INT
)

insert into @SuitableID (ID)
values(1),(2),(3)
RETURN (
 SELECT TOP 1 ID
 FROM @SuitableID
 ORDER BY (SELECT [NewId] FROM GetNewID)
)
END
GO

SELECT dbo.GetRandomID()