ignore division by 0 in update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Avi - 28 Aug 2008 15:21 GMT

Hi all,

I have a dynamic sql update statement where the expression in the update is
sent as a textual parameter to the sp. I only add it to my dynamic update
statement without parsing it. When one or more of the rows cause a division
by 0 error, non of the other rows get updated. How can I ignore the error
for the few rows that have the problem and update all the rest?

Thanks,

Avi

Reply to this Message

Plamen Ratchev - 28 Aug 2008 15:36 GMT

Add logic to your update statement to handle the 0 divisor values:

COALESCE( x / NULLIF( y, 0), 0)

or

CASE WHEN y = 0 THEN 0 ELSE x / y END

Signature

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

--CELKO-- - 28 Aug 2008 16:43 GMT

>> I have a dynamic SQL update statement where the expression in the update is sent as a textual parameter to the stored procedure. <<

Have you ever had a course in basic software engineering where they
mentioned the concept of cohesion in software modules? I hope that
this procedure is properly named and declared:

CREATE PROCEDURE Might_do_any_damn_thing
(unpredictable_code VARCHAR(1000))
BEGIN ..
END;

Reply to this Message

steve dassin - 29 Aug 2008 01:50 GMT

This is directed to developers.
To talk about cohesion in sql in terms of reusability is pretty silly. How
can you have reusability when the object of the exercise, a table, isn't a
variable!:) In sql the choice is procedure bloat or dynamic sql, both a
consequence of not having a variable to operate on. Anyone who says
differently is bloviating with smoke:)

http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

>>> I have a dynamic SQL update statement where the expression in the update
>>> is sent as a textual parameter to the stored procedure. <<
[quoted text clipped - 7 lines]
> BEGIN ..
> END;

Reply to this Message

Avi - 31 Aug 2008 13:36 GMT

I solved my problem with the following:

SET ARITHABORT OFF SET ANSI_WARNINGS OFF
SELECT 1/0

> Hi all,
>
[quoted text clipped - 7 lines]
>
> Avi

Reply to this Message