October 10, 2002 at 8:13 am
How do we perform error-handling inside a UDF? I've tried using RAISERROR as well as a ROLLBACK but that's not allowed.
As a minimum I'd like to be able to trap for certain conditions (which I can do) and come straight out of the function without returning anything (except for an error message or number).
Any ideas?
Thanks,
Karl
Karl Grambow
October 15, 2002 at 7:28 am
I've just been reading about error handling and, therefore, looked up your query.
"The RAISERROR and PRINT statements are not valid within a UDF. To detect any errors, you must use the RETURN statement and check for any errors when the UDF returns to the calling routine." (SAMS, Teach Yourself Transact-SQL)
Emma Smyth
ESmyth
October 15, 2002 at 8:28 am
OK, RAISERROR and ROLLBACK are out. Does anyone know whaat to do instead?
Steve
Steve Miller
October 15, 2002 at 9:03 am
Beats me,
I know that I can use the return statement. The problem is that in this particular function I'm returning a datetime, which limits what I can return. If I use the function within an INSERT statement then it becomes difficult to trap for a certain condition using the return statement because I'll be returning something, which I don't always want to do. On meeting a certain condition, I want to return nothing and also rollback the entire statement that called the function.
I can't believe there is no useful way of error-handling inside a function.
Karl Grambow
October 15, 2002 at 9:04 am
Beats me,
I know that I can use the return statement. The problem is that in this particular function I'm returning a datetime, which limits what I can return. If I use the function within an INSERT statement then it becomes difficult to trap for a certain condition using the return statement because I'll be returning something, which I don't always want to do. On meeting a certain condition, I want to return nothing and also rollback the entire statement that called the function.
I can't believe there is no useful way of error-handling inside a function.
Karl Grambow
October 15, 2002 at 1:21 pm
Sounds like plan B will have come into play then? Error trapping via the insert with a constraint / trigger, if the udf can return an "invalid" date. Example 1 Jan 1900.
October 15, 2002 at 1:43 pm
Are you saying to purposely give an invalid value in the return statement? Maybe my mind is a bit foggy today, but it seems to me if we don't have any way to trap the error, we won't have the chance to set the value.
Steve
Steve Miller
October 15, 2002 at 1:58 pm
Declare @ReturnDate Datetime
Select @ReturnDate = fn_SomeFunc(@SomeArg)
If (@ReturnDate = '1 Jan 1900')
-- Error Returned, Rollback etc.
Else
-- Commit Transaction
October 15, 2002 at 2:25 pm
OK, you're using an invalid date in the case where a function returns an invalid datetime.
So maybe in your function you'd do something like this:
----------------------------
IF @@ERROR != 0 GOTO Fail
...
Fail:
RETURN '1 JAN 1900'
----------------------------
Then in the stored procedure, use @@ERROR to do the RAISERROR, etc.
Not perfect, but better than nothing, I suppose.
Steve
Steve Miller
October 16, 2002 at 12:49 am
quote:
Declare @ReturnDate DatetimeSelect @ReturnDate = fn_SomeFunc(@SomeArg)
If (@ReturnDate = '1 Jan 1900')
-- Error Returned, Rollback etc.
Else
-- Commit Transaction
This works well in the above example but what if you're doing the following.....
BEGIN TRAN
INSERT INTO <sometable>
SELECT fn_SomeFunc(@SomeArg)
...
In this example, the return date, as long as it's a valid datetime value, will get inserted into the table. Unless I use a WHERE clause. What I really want to do though is rollback the whole INSERT statement.
The only way I've managed to do this is from within my function, on meeting a certain condition, return a string (any string). This causes the function to error, which I can then trap outside the function something like this:
FUNCTION:
RETURNS datetime
IF <certain condition> IS TRUE
RETURN 'any string' --this causes error because the function is supposed to return a datetime value
OUTSIDE FUNCTION:
BEGIN TRAN
INSERT INTO <sometable>
SELECT fn_SomeFunc(@SomeArg)
IF @@ERROR <> 0 ROLLBACK TRAN
It's a bit fiddly but by forcing an error within the function I can do it. Not sure if it's the best way though.
Karl Grambow
October 16, 2002 at 8:10 am
>> RETURN 'any string' --this causes error because the function is supposed to return a datetime value
🙂 Reminds me of the old days. Force an error when you want an error to trap. A bit "fuddy", but it works, unless someone knows of a better way.
Off topic: I asked my English colleague over the wall about the word "fuddy", since I've never heard the term before. He said it's usually used with "fuddy duddy". Thankfully he did confirm that it's not a bad or "rude" word. 🙂
Steve
Steve Miller
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply