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?

Check this question: stackoverflow.com/questions/772517/…

Nenad Zivkovic
– Nenad Zivkovic

2017-09-15 12:01:45 +00:00
Commented Sep 15, 2017 at 12:01 — Nenad Zivkovic
– Nenad Zivkovic, Commented Sep 15, 2017 at 12:01

Roger Wolf · Accepted Answer · 2017-09-15 12:22:20Z

2

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;

answered Sep 15, 2017 at 12:22

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A. Stam Over a year ago

Thanks to the comment by Nenad, I'd already been put on this track. This is indeed a working solution.

John Cappelletti Over a year ago

Interesting. I have similar views but never considered combining them +1

SqlKindaGuy · Accepted Answer · 2017-09-15 12:26:58Z

0

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

answered Sep 15, 2017 at 12:26

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

Comments

Ryan Gadsdon · Accepted Answer · 2017-09-15 12:00:57Z

-1

    select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

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

answered Sep 15, 2017 at 12:00

Ryan Gadsdon

2,4068 gold badges38 silver badges62 bronze badges

2 Comments

A. Stam Over a year ago

This returns the same error as using NEWID(): Invalid use of a side-effecting operator 'Crypt_Gen_Random' within a function.

Ryan Gadsdon Over a year ago

create it as a function and then call it

Collectives™ on Stack Overflow

Return a random row number using a function in SQL Server

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related