Using SP in UDF

Question

Using SP in UDF

Alaster07

SSC Enthusiast

Points: 135
More actions
October 12, 2016 at 1:25 pm

#331213

I am trying to create a function that will generate a random datetime value. Due to being unable to use NEWID() and RAND() in UDFs, I created two SPs to generate the date part and time part.
CREATE PROC [dbo].[GenerateRandomDate]
(
@RandomDate DATETIME OUTPUT,
@MinimumDate DATETIME = NULL,
@MaximumDayDifference INT = NULL
)
AS
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
SET @RandomDate = CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM(NEWID()) % @MaximumDayDifference), @MinimumDate))
END
CREATE PROC [dbo].[GenerateRandomTime]
(
@RandomTime TIME OUTPUT,
@MaximumSecondsFromMidnight INT = NULL
)
AS
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
SET @RandomTime = DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
I created a function that calls these two SPs.
CREATE FUNCTION GenerateRandomDateTime
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
DECLARE @RandomDate DATETIME
EXEC GenerateRandomDate @RandomDate OUTPUT, @MinimumDate, @MaximumDayDifference
DECLARE @RandomTime TIME
EXEC GenerateRandomTime @RandomTime OUTPUT, @MaximumSecondsFromMidnight
RETURN @RandomDate + @RandomTime
END
I was wanting to make it a function so I could use it in an update statement directly. When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Smendle Hall of Fame Points: 3969 More actions · Answer 1

Alaster07 (10/12/2016)
I am trying to create a function that will generate a random datetime value. Due to being unable to use NEWID() and RAND() in UDFs, I created two SPs to generate the date part and time part.
CREATE PROC [dbo].[GenerateRandomDate]
(
@RandomDate DATETIME OUTPUT,
@MinimumDate DATETIME = NULL,
@MaximumDayDifference INT = NULL
)
AS
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
SET @RandomDate = CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM(NEWID()) % @MaximumDayDifference), @MinimumDate))
END
CREATE PROC [dbo].[GenerateRandomTime]
(
@RandomTime TIME OUTPUT,
@MaximumSecondsFromMidnight INT = NULL
)
AS
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
SET @RandomTime = DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
I created a function that calls these two SPs.
CREATE FUNCTION GenerateRandomDateTime
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
DECLARE @RandomDate DATETIME
EXEC GenerateRandomDate @RandomDate OUTPUT, @MinimumDate, @MaximumDayDifference
DECLARE @RandomTime TIME
EXEC GenerateRandomTime @RandomTime OUTPUT, @MaximumSecondsFromMidnight
RETURN @RandomDate + @RandomTime
END
I was wanting to make it a function so I could use it in an update statement directly. When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.

Not sure why you think you need to create the first two as procs. You aren't getting any performance benefit by creating them as procs. They are functions so just create them as such and then you can call them from your third function or if you don't want to create 3 functions you could integrate the two procs(functions) into your third.

Alaster07 SSC Enthusiast Points: 135 More actions · Answer 2

Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 3

Alaster07 (10/12/2016)
Is there an alternative to what I am trying to do?

Not really. Keep in mind that a function must consistently return the same value for the same input values. SQL expects that behavior and will work incorrectly without it, since it will cache prior results to satisfy future calls to the function.

I think you'll need to use a stored proc. But, you could call the proc once and have it load a given number of random values into a table, which you could then use as you needed to, within say an inline-table-valued function.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Smendle Hall of Fame Points: 3969 More actions · Answer 4

Alaster07 (10/12/2016)
Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.

You are correct. My Apologies as penance I offer this as a solution.

Create View rndView

AS

SELECT RAND() rndResult

GO

CREATE FUNCTION RandFn()

RETURNS DECIMAL(18,18)

AS

BEGIN

DECLARE @rndValue DECIMAL(18,18)

SELECT @rndValue = rndResult

FROM rndView

RETURN @rndValue

END

GO

CREATE FUNCTION GenerateRandomDateTime

(

@MinimumDate DATETIME,

@MaximumDayDifference INT,

@MaximumSecondsFromMidnight INT

) RETURNS DATETIME

BEGIN

DECLARE @RandomDate DATETIME

BEGIN

IF @MinimumDate IS NULL

SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())

IF @MaximumDayDifference IS NULL

SET @MaximumDayDifference = 365

SET @RandomDate = (CONVERT(DATE, DATEADD(DAY, ( (dbo.RandFn()*@MaximumDayDifference) % @MaximumDayDifference), @MinimumDate)))

END

BEGIN

DECLARE @RandomTime TIME

IF @MaximumSecondsFromMidnight IS NULL

SET @MaximumSecondsFromMidnight = 86400

SET @RandomTime = (DATEADD(SECOND, ( dbo.RandFn() * @MaximumSecondsFromMidnight), CAST('00:00:00' AS TIME)))

END

RETURN @RandomDate + @RandomTime

END

SELECT dbo.GenerateRandomDateTime('01/01/2016 23:59:59',5,20000)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Alaster07 (10/12/2016)
Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.

Try an inline table-valued function instead.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

drew.allen SSC Guru Points: 76944 More actions · Answer 6

The standard workaround is to create a view that uses RAND() and/or NEWID() and then select from the view in the UDF.

And the reason they can't be used is that they alter the state of the database. It has nothing to do with being non-deterministic.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Here's an alternative that should be faster than using a scalar function. It includes an example on how to use it.

CREATE VIEW vNEWID

AS

SELECT NEWID() AS _NewID;

GO

CREATE FUNCTION GenerateRandomDateTime

(

@MinimumDate DATETIME,

@MaximumDayDifference INT,

@MaximumSecondsFromMidnight INT

) RETURNS TABLE

AS

RETURN

SELECT DATEADD( SS,

ABS( CHECKSUM(v1._NewID)) % ISNULL(@MaximumSecondsFromMidnight, 86400),

DATEADD(DAY,

ABS(CHECKSUM(v2._NewID) % ISNULL(@MaximumDayDifference, 365)),

ISNULL(@MinimumDate, DATEADD(YEAR, DATEDIFF( YEAR, 0, GETDATE()), 0))

)

) RandomDate

FROM vNEWID v1, vNEWID v2;

GO

SELECT rndDT.*, t.*

FROM sys.tables t

CROSS APPLY GenerateRandomDateTime(t.create_date, max_column_id_used, NULL) rndDT;

GO

DROP FUNCTION GenerateRandomDateTime;

DROP VIEW vNEWID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Alaster07 (10/12/2016)
When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.

But you can select from a view within a function.

And view can contain RAND(), NEW_ID(), and all sorts of other things.

_____________
Code for TallyGenerator

Alaster07 SSC Enthusiast Points: 135 More actions · Answer 9

Based on the feedback in this thread, I have changed the SPs to UDFs and am using a view instead of calling the NEWID() and RAND() functions directly.

CREATE VIEW vSeedData

AS

SELECT NEWID() GuidSeed,

RAND(CAST(NEWID() AS VARBINARY)) DecimalSeed

CREATE FUNCTION [GenerateRandomDate]

(

@MinimumDate DATETIME,

@MaximumDayDifference INT

) RETURNS DATETIME

BEGIN

IF @MinimumDate IS NULL

SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())

IF @MaximumDayDifference IS NULL

SET @MaximumDayDifference = 365

RETURN CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM((SELECT GuidSeed FROM vSeedData)) % @MaximumDayDifference), @MinimumDate))

END

CREATE FUNCTION [GenerateRandomTime]

(

@MaximumSecondsFromMidnight INT

) RETURNS TIME

BEGIN

IF @MaximumSecondsFromMidnight IS NULL

SET @MaximumSecondsFromMidnight = 86400

RETURN DATEADD(SECOND, (SELECT DecimalSeed FROM vSeedData) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))

END

CREATE FUNCTION [GenerateRandomDateTime]

(

@MinimumDate DATETIME,

@MaximumDayDifference INT,

@MaximumSecondsFromMidnight INT

) RETURNS DATETIME

BEGIN

RETURN dbo.GenerateRandomDate(@MinimumDate, @MaximumDayDifference) + dbo.GenerateRandomTime(@MaximumSecondsFromMidnight)

END

Usage: SELECT dbo.GenerateRandomDateTime('2016-10-01', 12, NULL)

I wanted to keep them as three separate functions so I could also generate just a date and time separately without having to do extra casting.

Thanks to everyone that participated in this thread.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Alaster07 (10/12/2016)
Based on the feedback in this thread, I have changed the SPs to UDFs and am using a view instead of calling the NEWID() and RAND() functions directly.
CREATE VIEW vSeedData
AS
SELECT NEWID() GuidSeed,
RAND(CAST(NEWID() AS VARBINARY)) DecimalSeed
CREATE FUNCTION [GenerateRandomDate]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT
) RETURNS DATETIME
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
RETURN CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM((SELECT GuidSeed FROM vSeedData)) % @MaximumDayDifference), @MinimumDate))
END
CREATE FUNCTION [GenerateRandomTime]
(
@MaximumSecondsFromMidnight INT
) RETURNS TIME
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
RETURN DATEADD(SECOND, (SELECT DecimalSeed FROM vSeedData) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
CREATE FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN dbo.GenerateRandomDate(@MinimumDate, @MaximumDayDifference) + dbo.GenerateRandomTime(@MaximumSecondsFromMidnight)
END
Usage: SELECT dbo.GenerateRandomDateTime('2016-10-01', 12, NULL)
I wanted to keep them as three separate functions so I could also generate just a date and time separately without having to do extra casting.
Thanks to everyone that participated in this thread.

With these 3 functions as they are developed you're already doing many more CASTs than necessary.

Not to mention of overhead of every UTF (especially scalar UTF) call.

It's so significant then 100 extra CASTs cannot match it.

As well DATE and TIME data types - processing of values of those types is about 3 times slower comparing to DATETIME.

Try this approach:

ALTER VIEW vSeedData

AS

SELECT RAND(CHECKSUM(NEWID())) Seed

GO

alter FUNCTION [GenerateRandomDateTime]

(

@MinimumDate DATETIME,

@MaximumDayDifference INT,

@MaximumSecondsFromMidnight INT

) RETURNS DATETIME

BEGIN

RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)

FROM vSeedData

)

END

GO

Now - if you want "dates only" - pass MaximumSecondsFromMidnight = 0.

If you need "time only" - SELECT dbo.GenerateRandomDateTime(0, 0, NULL).

You may convert (implicitly or explicitly ) the DATETIME value returned by the function to any data type you need at the end.

_____________
Code for TallyGenerator

DesNorton SSC-Insane Points: 24277 More actions · Answer 11

Sergiy (10/12/2016)

Try this approach:

ALTER VIEW vSeedData

AS

SELECT RAND(CHECKSUM(NEWID())) Seed

GO

alter FUNCTION [GenerateRandomDateTime]

(

@MinimumDate DATETIME,

@MaximumDayDifference INT,

@MaximumSecondsFromMidnight INT

) RETURNS DATETIME

BEGIN

RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)

FROM vSeedData

)

END

GO

Hi Sergiy

For my education .... What is the reason for the TOP 1 in the function?

How to post data/code on a forum to get the best help.

Sergiy SSC Guru Points: 110206 More actions · Answer 12

DesNorton (10/12/2016)

Sergiy (10/12/2016)
Try this approach:
ALTER VIEW vSeedData
AS
SELECT RAND(CHECKSUM(NEWID())) Seed
GO
alter FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)
FROM vSeedData
)
END
GO
Hi Sergiy
For my education .... What is the reason for the TOP 1 in the function?

It's a scalar function, can return only 1 value.

If anyone modifies the view to return more than 1 Seed, the function would start failing, if TOP 1 would not be there.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 13

As previously pointed out, I strongly recommend conversion of the scalar functions to iTVFs (inline Table Valued Functions). Basically, if the function has a BEGIN in it, it's not an iTVF and will be slower than if it was.

Please see the following article for a demonstration of what I'm talking about.

How to Make Scalar UDFs Run Faster [/url]

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

Alaster07 SSC Enthusiast Points: 135 More actions · Answer 14

@sergiy

I tried your approach, and it kind of mostly works. The issue I am running into is that the "early" times will all be at the front of the result set and all of the "late" times will be at the end of the result set.

dbo.GenerateRandomDateTime('2016-01-01', 287, 43200)

Here's the result set on ~10,000 rows. http://pastebin.com/8eVJFHWt

As you can see, the beginning of the year has the lower times and the end of the year has the higher times. I assume this is because both the date and time parts are using the same seed to "randomize"?