There is a difference in how errors are processed depending on whether the error occurs as part of executing a stored-procedure statement or instead it occurs while returning rows from a result set of the procedure that has been opened without error. Errors that are detected while processing a result set are not handled by the EXCEPTION clause if it is present. Instead, the error is returned to the consumer. This explains the original question because in the second case, the error is detected when the row is fetched from the result set.

As noted by others, there is a difference in behavior with this statement between different versions of SA. The behavior change stems from change 560044. This change causes the error to get generated as rows are fetched from the statement instead of when it is opened. This timing change only affects errors generated with expressions over constants. If the statement had contained something like the following, then behavior is unchanged (and an error is reported to the client):

begin
select (YMD( dummy_col,1,1)) from sys.dummy;
EXCEPTION when others then
end

With this example, the error can not be detected when the result set is opened, instead it occurs as the rows are fetched. As such, the error is not processed with the EXCEPTION clause and instead it is returned on the fetch.

The purpose of change 560044 was not to affect the way exceptions are processed; the change is intended to avoid generating spurious errors that occur while constant expressions are evaluated at open time. In some cases, these constant expressions generated errors but the proper result set could be returned without evaluating the expressions (for example, because the result set was empty, or because other conditions prevented the expression from being needed). The change defers the error until the expression value is definitely needed.

Breck, can you explain further (here or open a support case) how this change prevents you moving to a newer version. There may be some further refinements we can make to the change if we know the situations where you think the error should be generated at open time.

@Ivan: After your description I understand the differences between the situations.

My original goal was to handle ANY error with the exception handler, but it seems that some errors are unable to be handled and are passed on to the user. Are there any other ways to handle these errors?

This looks like a bug undocumented behavior change in Version 12, both the GA build 12.0.0.2483 and the first published EBF 12.0.0.2566:

An exception raised by a SELECT that
returns a result set from a SQL
block will bypass the subsequent EXCEPTION
handler in that block.

Here's your code in V11 and V12: a simple BEGIN END block in dbisql with a exception raised in the SELECT that dbisql would otherwise display in the results tab. Version 11 behaves as expected (no result set, no message), but Version 12 throws that funky dbisql dialog box (it should not).

begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end
-- 11.0.1.2276: no result set in dbisql, no error message
-- 12.0.0.2483: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"
-- 12.0.0.2566: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

If you wrap the failing SELECT inside a PROCEDURE and SELECT from that procedure, the behavior in V11 is slightly different but also expected (empty result set, but still no message). However, in V12 it is still wrong funky.

CREATE PROCEDURE p()
begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end;
SELECT * FROM p();
-- 11.0.1.2276: empty result set, no error message
-- 12.0.0.2483: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"
-- 12.0.0.2566: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

I have a whole jackwagon full of code that depends on the V11 behavior, which means I've got a whole lot of work to do before moving it to 12... if I had any idea how to do it :)

jackwagon - Freight wagon or Chuck wagon (which held supplies) typically pulled by mules. Usually the slowest wagon in a wagon train. Worst job in a wagon train, being at the back, eating all the dust, dirt and smell from the front. Mules are identified as Jacks or Jenny depending on the sex of the mule. http://www.webanswers.com/answer/1331703/misc/what-is-a-jack-wagon-9a4409

It gets worse... I may not be able to
move off build 11.0.1.2276 to a later
EBF; see Volker's comment.

@Breck: Besides that, I really love your usage of the "catch" phrase: In well-known programming languages, that's the equivalence of SA's exception clause. As such, I feel you are thrown on your own exception-handling for your "Update MyApplication set version = V12;" statement. - But that's no fun, indeed:(

Oh, "lack of coffee" must be the reason: I focussed on the missing ";" as the one difference, completely missing the execute/select change in line 4. And for sure: Your observation is more interesting:)

@Breck: Certainly did. Edited
@Volker: I did not know that about mixing the dialects in batches. As I am picking up the language and taking some snippets from the book and copying some code from a blog I will be aware of that now, so please leave this answer even though I edited the question.

Due to that change, the evaluation of some constant expressions has changed from the time when cursors are opened to the time when values are fetched. I'm told that exception handlers only catch errors that occur during open, not fetch. Strictly speaking, then, the new behaviour is still "correct" but we also never like to change such fundamental behaviour in an EBF. From here, I'll let the developers who deal with those components figure out what, if anything, must be done. It's not my area of expertise.