March 26, 2008 at 9:01 pm
Hi All
I did some googling and after referring to article http://www.sqlservercentral.com/articles/News/exceptionhandlinginsqlserver2005/2237/
If its not possible to use TRY CATCH kind of error handling in UDFs, then how most of the DBA's set error handling standards?
I did some testing and was able to use @@ERROR in UDFs but with @@ERROR i have to handle all the errors every SQL query. Is there any better way to get around this and use some sort of TRY CATCH in UDFs.
I found TRY CATCH better than @@ERROR. The reason i am stressing on TRY CATCH is that i am writing a document stating SQL standards and error handling is part of it.
Any help is appreciated.
Anam
March 26, 2008 at 9:05 pm
I dont think you can use try catch. You will have to take the tedious road for udfs.
This link may help you.
March 26, 2008 at 10:57 pm
Hi Adam
The link you provided is really good.
But unfortunately i am back in the grave. The test i did with @@ERROR also failed. It compiles but fails when i execute the function, it doesnt capture the error.
the function i created is below
ALTER FUNCTION dbo.testfnc()
RETURNS datetime
BEGIN
declare @tmpuser datetime
SELECT @tmpuser = convert(datetime, '2003123')
-- i am passing getdate as test only to check if control goes to IF statement
IF @@error <> 0
SELECT @tmpuser = getdate()
RETURN @tmpuser
END
SELECT dbo.testfnc()
The error message i got after execution is expected one as i am using wrong value.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Please help me out in handling errors in UDFs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply