Use GETDATE() within a UDF
-
recently i had a need to use GETDATE() within a function. The result?
Server: Msg 443, Level 16, State 1, Procedure ProofOfConcept, Line 10
Invalid use of 'getdate' within a function.
I looked around the internet and could not find a workaround, so i made my own:
-- create a view that can be accessed from a function
CREATE VIEW dbo.vw_GetDate
AS
SELECT GETDATE() AS Now
GO
-- create a reusable function for quickly and easily getting date-time
CREATE FUNCTION dbo.Now()
RETURNS datetime
AS
BEGIN
DECLARE @Tmp datetime ;
SELECT @Tmp = Now FROM vw_GetDate ;
RETURN @Tmp ;
END
GO
-- now use it in a proof of concept function
CREATE FUNCTION dbo.ProofOfConcept()
RETURNS datetime
AS
BEGIN
--RETURN GETDATE() ; -- this no workie workie
RETURN dbo.Now() ;
END
GO
-- now use it in a sentance
SELECT dbo.ProofOfConcept() AS [GETDATE()]
-
There is no case when you need GETDATE() within a UDF.
_____________
Code for TallyGenerator -
Sergiy is absolutely correct.
If a UDF needs the current date, pass the value as a parameter.
Here is a story regarding the use of UDFs that include table access.
About 2 years ago, a prior client called on a Wednesday regarding major performance problems with a new system that ran for the first time on Monday.
They had developed the db portion with extensive use of UDF that included table access. That week-end, I and another DBA ripped out all of the UDFs and replaced with the appropriate sql statements resulting in solving the performance problem.
Do not put table access in stored procedures !
SQL = Scarcely Qualifies as a Language
-
You mean in functions?
_____________
Code for TallyGenerator -
Sergiy: Thanks for catching the typing error
"Do not put table access in stored procedures !"
should be
"Do not put table access in FUNCTIONS!"
SQL = Scarcely Qualifies as a Language
-
Well, let me disagree with the absolutness of that statement. It depends. You can very well put table access inside UDF's, sometimes it's even the best thing to do - BUT... everyone that is thinking 'UDF' as a solution need to be very clear and understand that UDF's are not a magic wand that will magically transform, say a cursor to a setbased solution - it's rather the opposite. If not careful, it's more likely that a perfectly good setbased piece of code will instead be turned into a 'hidden cursor' if UDF's are used without discretion.
So, it depends. I'd say 'be careful' - I won't stretch it as far as to say 'never access tables in UDFs'.
..just my .02 though
/Kenneth
-
You are absolutely right about "hidden cursor".
But the best and most reliable way to create hidden cursor is to put table access inside UDF!
Yes, nothing is absolute, and sometimes table access in UDF may be used, as well as cursors.
But very, VERY, VERY CAREFUL, rarely, better never.
Right function produces result from parameters supplied. And not from anything else. Otherwise result of the function is unpredictable.
If you need to workout values in tables, put function into view.
But not view into function.
P.S. And there is still one "never" - you never need GETDATE() inside UDF.
_____________
Code for TallyGenerator -
P.S. And there is still one "never" - you never need GETDATE() inside UDF.
Just curious, can you expand on this?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
-
I asked first
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Theory:
Getdate() is just datetime value. It may be supplied to UDF as a parameter. You set GETDATE() value to this parameter when you call UDF. It makes UDF deterministic and more universal.
Practice:
In physics there is a principle: it's not possible because it could never happen.
In other words, if there are no cases when it's possible, it's considered impossible.
Show me the case when you need it.
_____________
Code for TallyGenerator -
When you reread my first reply closely, you'll noticed that I didn't say "I need it". Actually I don't need it, but you made this bold statement, so I thought I'd ask for your reasoning here.
Coming to think about it, there might be some valid reasons when you have a table-valued function. Yes, I am aware that a parameterless inline TVF is basically nothing but a view. However, there might be cases where you need conditional or more complex logic which you can't put in a view. UDF's allow you to define variables and allow procedural code. And depending on your requirements you might need GETDATE() for your code for some reasons. Sure, you can use a stored procedure, but what when you need to join the results to some other table?
I wouldn't say "Never". As Kenneth would say: "It depends..."
Btw, using GETDATE() inside a view and call this view from the UDF can yield incorrect results:
Consider this:
USE northwind
GO
CREATE VIEW foolview
AS
SELECT GETDATE() AS Jetzt
GO
CREATE FUNCTION fool_me()
RETURNS DATETIME
AS
BEGIN
RETURN
(
SELECT Jetzt
FROM foolview
)
END
GO
CREATE function you_dont_fool_me(@Jetzt datetime)
RETURNS DATETIME
AS
BEGIN
RETURN @Jetzt
END
GO
DECLARE @Jetzt datetime
SET @Jetzt = GETDATE()
--Test 1 viele Zeilen
SELECT DISTINCT dbo.fool_me()
FROM [Order Details] AS od
INNER JOIN Orders AS o
ON o.OrderId = od.OrderID
--Test2 eine Zeile
SELECT DISTINCT dbo.you_dont_fool_me(@Jetzt)
FROM [Order Details] AS od
INNER JOIN Orders AS o
ON o.OrderId = od.OrderID
GO
DROP FUNCTION fool_me
DROP FUNCTION you_dont_fool_me
DROP VIEW foolview
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I may change my mind on UDFs.
I now believer "deny create function to public" is the most appropriate.
Public includes everyone except system adminstrators and does include users with ddl_admin or db_owner database roles.
I am in the process of writting an article and a set of test cases but here is an interesting post:
From a sql-server-performance posting (edited):
When using a UDF in the where, run time goes from < .01 seconds to 2.14 minutes (134 seconds)
-- Comment this is 13 thousand times slower
The SQL is:
SELECT ExpenseDetailID
FROM ExpenseDetail
WHERE UPPER(SUBSTRING(dbo.Dec(ReferenceNumber)
,CHARINDEX('-',dbo.Dec(ReferenceNumber))+ 1
, LEN(dbo.Dec(ReferenceNumber)))) = 320
AND Flag 'D'
The object "dbo.Dec" is a user defined function.
SQL = Scarcely Qualifies as a Language
-
I was calling a function from a stored proc that accepted 3 parameters and returned a value based on how those parameters related to each other.
4th parameter would have been GETDATE(), but I felt it was an annoyance to have to pass the current date-time when calling the function, when the system could just as well produce that value all by itself inside the function.
I don't think querying a view is going to have performance issues similar to a table scan, as was your example.
-
It's an annoyance to pass a parameter but not to have a view dedicated just to display GETDATE()?
And when one day you'll need to compare dates in function not to current date but to the same moment yesterday whay you're gonna do? Write another function or reser system clock?
And what if you use a server placed in another time zone?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply