Problem calling a Non-Deterministic UDF in a Select Statement
-
I have a UDF (calculate business hours) that calls another UDF (get start and end of business hours per day) within it. SQL Server lists them both as non-deterministic.
This works:
DECLARE
@x as int
Exec @x = fnCalcBusHours 1, 1, '8/31/2004 10:18:30 PM', '9/5/2004 4:23:35 PM'
PRINT
@x
This doesn't work:
select
Incident_Number, fnCalcBusHours(Cust_Id, Service_Id, open_time, close_time) from tablename
I need to return compliance based on business hours for several select statements which are bound to reports. Will post a larger section of code on request, but I'm pretty sure someone will be able to see my problem without it. Looks like I'm violating rules somewhere, but I don't know how to fix. Also tried same code in sproc form instead of udf and get this error: 'spCalcBusHours' is not a recognized built-in function name.
-
pretty much all functions have to be preceeded by the owner (usually dbo.)
this will work:
select Incident_Number, dbo.fnCalcBusHours(Cust_Id, Service_Id, open_time, close_time) from tablename
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Tried that but it didn't work. Get this error:
sg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function.
Also tried the same with a stored procedure version and get this:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.spCalcBusHours", or the name is ambiguous.
Search on sys.objects can find both:
fnCalcBusHours 1495676376 NULL 1 0 FN SQL_SCALAR_FUNCTION
spCalcBusHours 1527676490 NULL 1 0 P SQL_STORED_PROCEDURE
Any other suggestions? Again, I can post all the code if it will help.
-
does this return a value? this should do the same thing you did when assigning it to the @x variable.
SELECT dbo.fnCalcBusHours( 1, 1, '8/31/2004 10:18:30 PM', '9/5/2004 4:23:35 PM')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Same error:
Msg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function.
I think technically it should work fine. But it will only run under EXEC and not through SELECT, even if you provide specific values instead of variables. I can send you the code for both functions involved if it will help, but I don't want to post it here b/c it is rather lengthy. Please let me know. (by the way, your quote is great for my problem
) -
based on the error messge, it sounds like the function is calling a sotred proc in it's body; if you want to send me a priv message witht eh function in it i'd be glad to look at it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes, that's correct. I have dynamic-sql in the 2nd function, which from what I can find is not allowed. This is irritating b/c 1/2 the people on the web say 'oh yeah - you can do that' when the reality is you can't.
Dymanic SQL cannot be used in a function.
I don't want to waste your time, so I'll go back to the drawing board. I'll msg you private later if I can't get anywhere. thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply