UDF Arithmetic error handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UDF Arithmetic error handling

Author

Topic

bakerjon
Posting Yak Master

145 Posts

Posted - 2007-03-01 : 13:55:30

Hey all,
I don't write a lot of UDFs, but I've run into an interesting problem with a developer who needs to do some math on a column in set based way. Seems like the best time for a fucntion! However, the process requires that he do some math in a couple of ways. If one errors, return the other value. I know I know, don't code so errors can happen. Any way...

How do you ignore an arithmetic error in a UDF without aborting or exiting the execution of the function?

Thanks in advance for the help!

Jon

Holding it down on the engineering tip, y'all!

http://www.sqljunkies.com/weblog/outerjoin

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-01 : 14:04:38

You might want to post the code...my esp usb port is clogged

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bakerjon
Posting Yak Master

145 Posts

Posted - 2007-03-01 : 14:19:51

Here is the code which resembles the actual process we are having issue with:

alter Function [dbo].[foo] (@portfolio_id int)
RETURNS DECIMAL(12,5)
AS
BEGIN
declare @ret decimal(19,2)
declare @ret2 decimal (19,2)

set @ret = null
select @ret = @portfolio_id / 0
select @ret2 = 2

RETURN isnull(@ret, @ret2)

END;

Thanks again for all your help

Jon

Holding it down on the engineering tip, y'all!

http://www.sqljunkies.com/weblog/outerjoin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 15:58:19

I assume that you really what to know how to disable errors. But, I would highly suggest that you code it such a fashion as to avoid generating an error.

Do you have a better example? Your division by zero error is always going to happen, so I am confused as to the point of it.

-Ryan

bakerjon
Posting Yak Master

145 Posts

Posted - 2007-03-01 : 16:26:52

This example is not a good one I agree..

In my code I have a Power function in place of the dive by zero statement and its in a loop. For some iteration say iteration 19 the power function exceeds the numeric value and gives overflow error and terminates there. The code should ideally skip that statement if that happens and go to the following statement.

Hope I did not confuse you more.

Thanks again for your response....

Jon

Holding it down on the engineering tip, y'all!

http://www.sqljunkies.com/weblog/outerjoin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 17:11:12

Perhaps there is a better way, but the only way I know how to do something like that in a UDF would be to check the value before your operation (assuming that is possible ij your case).

For example, working with INTs and trying to avoid an overflow:

IF (2147483647 - @CurrentValue) > @NextValue
    SET @ret = @CurrentValue + @NextValue
ELSE
    SET @ret = NULL

Something like that, but it still sounds bad to me.. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources