How to Form an Exception Handler

[former_SQLA_member1694868]

Why is it that in this case:

begin
declare @zero integer;
set @zero = 0;
execute (YMD(@zero,1,1));
EXCEPTION when others then 
end

The code executes without error,

while in this case:

begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end

I get the error:

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"

Breck has this article on exceptions already posted on his blog, but as I was experimenting I ran into the above.

EDITS:
Added ";" to second block as it should have been.
Using Version 11.0.1.2472

[Former Member · Accepted solution]

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.

[former_SQLA_member1694868 · Accepted solution]

@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?

[Breck_Carter · Accepted solution]

@Ivan: I am not woooorrrthy! Seriously, no support case, you have just explained why I sometimes see exceptions reach the browser window.

[Breck_Carter · Accepted solution]

@Ivan: Let's skip lunch today, let's just open another can of worms here at our desks: http://sqlanywhere-forum.sap.com/questions/1221/is-this-how-i-have-to-trap-exceptions-raised-by-the-...