Getdate() in user defined function
-
I thought I saw a thread in this earlier but I can't find it and it is exactly what I'm dealing with. So frustrated!!!
I found that though you can pass in getdate() as a param to an inline UDF, I can't to a table UDF. Further more I can't execute the getdate() function within a UDF....am I the only one who finds this absurd? I'm really hoping someone out there knows how to get around this because other wise I have to implement the most ridiculous work around. Any comments about this short coming in SQL are welcome but I'm holding out hope that one of you has figured a way around it.
K Leb -
You can fool it by using a view:
CREATE VIEW v_GetDate
AS
SELECT GETDATE() Now--Jonathan
--Jonathan -
Ugh! Yeah that's where I'm headed I guess, but selecting from a table, or a view that is created to house or supply, something that should be as easily and readily available as a system date seems so incredibly lame to me.
I just can't believe it's so limited...
K Leb -
quote:
Ugh! Yeah that's where I'm headed I guess, but selecting from a table, or a view that is created to house or supply, something that should be as easily and readily available as a system date seems so incredibly lame to me.I just can't believe it's so limited...
Microsoft deliberately tries to block UDFs from being nondeterministic. It's by design, not a technical limitation; the idea being that a set-based operation shouldn't vary row by row. If you do a select on a table and include GETDATE() in the select list, the system returns the same value for each row, even though it might take seconds (or minutes) to actually return the result set. That's deliberate, and they want UDFs to have the same behavior.
--Jonathan
--Jonathan -
...which I can appreciate, and so they don't allow for the getdate() function in the udf ...BUT passing it in as a param should be allowed. Te return value for the call at the time of execution should resolve itself and be passed in to the udf as the parameter. To me it's another one of the many time MS thinks it knows what I want to accomplish better than I do, and in the end they have hamstringed my ability to code well. I mean here I have this beautiful function that is going to eliminate the need for 4 views and two procs in my db and perform about 6 times faster than it was ...but I'm gonna have to create a meaningless view to return a date that I can use? Silly...
I am suprised that there isn't something else I can do...gonna keep digging.
K Leb -
quote:
...which I can appreciate, and so they don't allow for the getdate() function in the udf ...BUT passing it in as a param should be allowed. Te return value for the call at the time of execution should resolve itself and be passed in to the udf as the parameter. To me it's another one of the many time MS thinks it knows what I want to accomplish better than I do, and in the end they have hamstringed my ability to code well. I mean here I have this beautiful function that is going to eliminate the need for 4 views and two procs in my db and perform about 6 times faster than it was ...but I'm gonna have to create a meaningless view to return a date that I can use? Silly...I am suprised that there isn't something else I can do...gonna keep digging.
Why not just declare a local variable, set that to GETDATE(), and then pass that to the UDF?
--Jonathan
--Jonathan -
because the UDF is going to be used in search functionality as a view, I need join on it. When I realized I needed to pass in a date, I thought I would be able to pass in getdate(), like I do with my inline functions, so I was going to wrap the udf in a view that passed in getdate() as the param. Since I can't do that and I can't select getdate() in the view and pass it in, in the same view...I'll have to tier two views ... didn't want to have to do that.
Thanks for your input Jon...if you come up with something new and brillant...hit me with it.
K Leb
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply