vendredi 15 septembre 2017

Return a random row number using a function in SQL Server

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?




Aucun commentaire:

Enregistrer un commentaire