How to Form an Exception Handler

[#1]

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

[VolkerBarth]

For your 2nd example, I get a syntax error (SQLCODE -131) relating to the "set" in line 3 (tested with SA 11.0.1.2427).

That's reasonable as you are mixing Transact-SQL and Watcom-SQL dialects here, and that is not allowed in batches: The declare without a closing delimiter (no ';') is T-SQL, the set statement not.

It seems that a syncatically invalid batch won't be executed, and as such, neither the normal statements nor the exception handler are executed.

[Breck_Carter]

yes, but it gets interesting if you fix the syntax error... he probably broke the cardinal rule "Always copy and paste your code, never type it in from scratch."

[VolkerBarth]

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:)

[former_SQLA_member1694868]

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

[VolkerBarth]

@Siger: No need to worry, we don't delete answers here... Glenn Paulley has written a great overview over the two dialects (and the resulting parsing problems) in his blog, cf. http://iablog.sybase.com/paulley/2010/03/mixing-sql-dialects.