December 11, 2002 at 9:06 am
Hello - Me again...
Why can I not call my UDF using GetDate ???
CREATE FUNCTION _UDFTest (@MonthToRun datetime)
RETURNS Table AS
RETURN SELECT 'X' AS SomeData, @MonthToRun AS OtherStuff
This works...
select * from _UDFTest('12/11/2002')
This fails
select * from _UDFTest(GetDate())
and here is the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Note - I'm NOT using GetDate INSIDE the UDF - that will obviously fail.
THX - B
Edited by - BillyWilly on 12/11/2002 09:08:58 AM
December 11, 2002 at 9:22 am
I'm guessing this BOL statement is why:
...Built-in functions that can return different data on each call are not allowed in user-defined functions.....
Now you could do the following:
declare @MonthToRun as datetime
select @MonthToRun = getdate()
select * from _UDFTest(@MonthtoRun)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 11, 2002 at 9:26 am
Ya thanks -
I have that working - but it seems that any time I try to embed functions as parameters I get problems. I'd like to use T-SQL like a "Real" language.
Try CASTing the GetDate() explicitly into a datetime and it STILL fails - wtf !
December 11, 2002 at 9:36 am
I think even when you are casting the getdate() function as a datetime variable, you are really only passing a string to the udf, and the udf is doing the casting, and therefore violating the rule stated earlier.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 11, 2002 at 10:34 am
As noted, a function must return the same result every time it's called. Doesn't make a lot of sense to me but that's the rule. There are lots of restriction on UDF, notably no data base inserts/deletes.
There is a list of all restrictions in BOL that is worth reviewing prior to writing any function.
In your case, you can add a parm that accepts a datetime. That shifts the "variableness" to the caller from the function.
December 11, 2002 at 10:40 am
Heh Don...
Read my first post carefully - in fact - try it on your machine. My problem is that I AM passing in a datetime param.
All I want to do is use T-SQL like a real language - Like:
X = FancyFunction(GetAFancyParamFunction())
For Greg...
If I specify in the Function "DateTime" - why would it receive a String or something else - why bother even specifying a Parameter type at all?
No biggie - just another T-SQL irritant.
December 12, 2002 at 9:44 am
UDF's cannot use non-deterministic functions. It's the rule and SQL Server will complain if you try to violate it.
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
December 12, 2002 at 10:01 am
Again....
Please look at the source code.
*Important*
There are no non-deterministic functions in my example function and it compiles fine.
*Important*
The function compiles and runs fine.
Problems arise only when calling the function using a non-deterministic function.
In my mind - that should not matter at all.
What happens outside of the function is no business of the function at all. After all - that's WHY we have functions in the first place - to remove them completely from external influences.
Here it is again:
CREATE FUNCTION _UDFTest
(@MonthToRun datetime)
RETURNS Table AS
RETURN
SELECT 'X' AS SomeData,
@MonthToRun AS OtherStuff
This works...
select * from _UDFTest('12/11/2002')
This fails
select * from _UDFTest(GetDate())
- B
December 13, 2002 at 2:24 am
BillyWilly, if you are looking for a solution to your problem then Greg gave the solution in his first post.
If you are looking to just bitch about the TSQL language, why not start a new Topic "Bitching about TSQL"?
December 13, 2002 at 7:08 am
Hey funny guy...
1) Read my posts - I obviously have a "working" solution.
2) If you actually took the time to test my sample and have ever written a program before in your life - you'll see what I'm talking about.
3) I'm curious about this behavior and will continue to post and discuss until a SQL guru provides a reason.
I don't feel I'm bitching or doing anything wrong - just normal curiosity. Go ahead - be daring - try the sample - live life on the edge.
December 13, 2002 at 7:44 am
Yeah, I tried this and got the same. Interestingly if you do not return a table data type it works i.e.
CREATE FUNCTION UDFTest (@MonthToRun datetime)
RETURNS datetime AS
begin
return @MonthToRun
end
go
then to call
select dbo.UDFTest(getdate())
This works?
Regards,
Andy Jones
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply