Using rand() inside a function

Question

Using rand() inside a function

a_ud

SSCrazy

Points: 2457
More actions
August 3, 2015 at 6:59 am

#300915

Help needed.
I've got to modifiy a function that basically should concatenate 2 names and then should add a random value.
I've tried:
1) Calling rand() from inside the function > produces error
2) Calling a stored procedure existing in the DB that generates a rand no. between 2 integers (again it fails, you can't call stored procedures from functions 🙁 ).
Any suggestions you can come up with? I'm really a newbie so any straight forward solutions much appreciated. Other suggestions welcome as far as they are easy to implement.
Thanks in advance. p.
ALTER FUNCTION [dbo].[myfunction]
(
@pmVolunteerID varchar(8),
@pmPhase int
)
RETURNS nvarchar(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @return nvarchar(20)
DECLARE @alea int
--EXECUTE @alea=dbo.spRandBTW 100,999 CAN'T CALL this
--EXECUTE @alea=round(1000 * rand(),0) CAN'T USE this
IF @pmPhase = 2
BEGIN
-- Add the T-SQL statements to compute the return value here
SET @return = (
SELECT lower(left([FN],1)+left([SN],1)) + CAST(@alea as nvarchar)
FROM tblVolunteer
WHERE VolunteerID = @pmVolunteerID)
END
ELSE
BEGIN
SET @return = ''
END
-- Return the result of the function
RETURN @return
END

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Why MS made it so difficult to write functions with indeterminate results, I'll never know. You'll need to do the following hack...

First, create this view. The name of the function stands for "indeterminate Functions".

CREATE VIEW [dbo].[iFunction] AS

/**********************************************************************************************************************

Purpose:

This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such

a thing directly in the function. This view also solves the same problem for GETDATE().

Usage:

SELECT MyNewID FROM dbo.iFunction; --Returns a GUID

SELECT MyDate FROM dbo.iFunction; --Returns a Date

Revision History:

Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation

Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code (all comments). No logic changes.

**********************************************************************************************************************/

SELECT MyNewID = NEWID(),

MyDate = GETDATE();

GO

Then you can write your function something like the following...

ALTER FUNCTION dbo.myfunction

(

@pmVolunteerID VARCHAR(8),

@pmPhase INT

)

RETURNS NVARCHAR(20) AS

BEGIN

DECLARE @Return NVARCHAR(20)

;

IF @pmPhase = 2

SELECT @Return = LOWER(LEFT(FN,1)+LEFT(SN,1))

+ (SELECT CAST(ABS(CHECKSUM(MyNewID))%900+100 AS NVARCHAR(10)) FROM dbo.iFunction)

FROM tblVolunteer

WHERE VolunteerID = @pmVolunteerID

;

ELSE

SELECT @Return = ''

;

RETURN @Return

;

END

;

GO

For more information on how to easily generate random numbers and dates, please see the following articles...

http://www.sqlservercentral.com/articles/Data+Generation/87901/

http://www.sqlservercentral.com/articles/Test+Data/88964/

EDIT! Added a missing parenthesis.

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

a_ud SSCrazy Points: 2457 More actions · Answer 2

Thanks Jeff, perfect solution. Testing it now.

I also found this explanation, pretty much in the same line, that also seems easy enough:

http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.
I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/

Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (8/3/2015)
a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.
I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.

And nevermind that RAND() really is not all that random, since it becomes deterministic when I pass it any non-null parameter. But yeah - the perf is also poor :).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Matt Miller (#4) (8/3/2015)
Jeff Moden (8/3/2015)
a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.
I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.
And nevermind that RAND() really is not all that random, since it becomes deterministic when I pass it any non-null parameter. But yeah - the perf is also poor :).

You could use the only pseudo-random number generator in SQL Server as the operand for RAND(). That would be NEWID() but the conversion can't implicitly be done so you have to convert it and now it's even slower. 😉

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