Using non-deterministic T-SQL functions inside a UDF

Question

Using non-deterministic T-SQL functions inside a UDF

pietlinden

SSC Guru

Points: 63591
More actions
July 29, 2013 at 9:47 pm

#276379

Is it even possible to use a non-deterministic function like RAND() inside a UDF? If so, how?
I'm trying to write a function to generate semi-random numbers, and it lands flat on its face...
CREATE FUNCTION dbo.GetRandomNumber (@Lower int, @Upper int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Random int
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
RETURN(@Random)
END
I get this:
Msg 443, Level 16, State 1, Procedure GetRandomNumber, Line 7
Invalid use of a side-effecting operator 'rand' within a function.
If I'm reading it right (no guarantees, mind you!), I can't do this. If I'm inside a stored procedure, I can generate the numbers just fine... so I guess I could do that if I needed to.
So is there any way to get SQL Server to return a value for a non-deterministic T-SQL function to a Function I created?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use APPLY with a simple expression like this. Also, RAND operates as a run-time constant. NEWID doesn't, so you can generate a new value for each output row of your query;

DECLARE @min INT, @max INT

SELECT @min = 4, @max = 12

SELECT a.Name, x.srn

FROM SYSCOLUMNS a

CROSS APPLY (SELECT srn = ABS(CHECKSUM(NEWID()))%(@max-@min+1)+@min) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

There is a cheaty way to use a side-effecting function in a function, and that's to encapsulate it into a view, like this:

CREATE VIEW vw_srn AS SELECT srn = ABS(CHECKSUM(NEWID()))

GO

CREATE FUNCTION [dbo].[IF_SemiRandomNumber]

(

@min INT,

@max INT

)

RETURNS TABLE AS

RETURN (SELECT srn = v.srn%(@max-@min+1)+@min FROM vw_srn v

)

GO

SELECT * FROM IF_SemiRandomNumber (2,7)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pietlinden SSC Guru Points: 63591 More actions · Answer 3

Chris,

thanks for the examples. I'm going to read up more and see if I can get my head around Paul White's articles on CROSS and OUTER APPLY and see if I can sort it out. I think I get it, but it'll take a little playing with for it to sink in.

Pieter

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You can pass the random value to the function via an input parameter:

CREATE FUNCTION dbo.GetRandomNumber (@Lower int, @Upper int, @MyRand float)

RETURNS int

WITH SCHEMABINDING

AS

BEGIN

DECLARE @Random int

SELECT @Random = ROUND(((@Upper - @Lower -1) * @MyRand + @Lower), 0)

RETURN(@Random)

END

go

select RandNum = dbo.GetRandomNumber( 0, 2000, rand(binary_checksum(newid())) )

from

( select top 10 x=1 from sys.columns) a

order by

1

go

drop function dbo.GetRandomNumber

Results:

RandNum

-----------

22

25

141

840

1004

1053

1268

1312

1345

1793

(10 row(s) affected)

Jeff Moden SSC Guru Points: 1004617 More actions · Answer 5

I guess my question would be, why do you need a function to do this when the inline code is so easy?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Using non-deterministic T-SQL functions inside a UDF

Cookies on SQLServerCentral