[Trick] Eliminating "arithmetic overflow error" that aren't in the result set.

Ran into a problem where the SP would always kick out the standard overflow message:

Arithmetic overflow error converting numeric to data type varchar

As it turns out, the problem was data that exists in the data set, but not in the results set - the WHERE clause eliminated it. What made it even harder to troubleshoot was that because that was the issue, we could duplicate it by running the SP - but not by copying and pasting the code. No matter what options you used - ARITHABORT, ANSI_WARNINGs, etc, it would run successfully, skipping over the bad record (since it was excluded via the WHERE clause).

As is pointed out in "Defensive Database Programming with SQL Server" by Alex Kuznetsov (WELL worth getting, and Red-gate offers a free PDF), you can't guarantee the order things get evaluated in.

So one way around it: take your query that is failing in the SELECT clause. Find a unique key that you can pull from your data set. Copy/paste the entirety of the FROM/WHERE, and SELECT only this key into a temp table. Now go to your full query and INNER JOIN this temp table.

Another potential way around it: remove everything from the where clause and keep it in the JOINs.