NOOP in SQL procedure? And other questions

Hi y'all

I've been writing some SQL stored procedures lately - not external ones, just to be clear.

I find the SQL type of procedure to be very nice to use. Of course, some questions have come up.

1. When using the CASE control statement, I find that it requires an ELSE in this context. Now many times I don't need the ELSE - all conditions are covered in the WHEN clauses, and I don't want some "just anything" bit.

The docs have this information -

"If none of the conditions specified in the WHEN are true, and an ELSE clause is not specified, an error is issued at runtime, and the execution of the CASE statement is terminated (SQLSTATE 20000)."

So any suggestions for a NOOP? I've used a SET of a variable to itself. I suppose I could create a CONTINUE HANDLER.

2. Speaking of which - it seems that a HANDLER also requires a procedure statement - a NOOP would be nice sometimes - this would give a way to ignore certain "errors", like table already exists on a CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE - BTW, I really like the latter.

3. Again speaking of HANDLERs - I wanted multiple statements in a HANDLER the other day - didn't succeed. I may have the answer - is it another compound statement? BEGIN...END kind of thing, probably with matching labels?

Ah, here it is, in the docs -

"Support for nested compound statements enables the use of a compound statement within the declaration of a condition handler."

OK, my own answer to this one.

I find the documentation to be a little short on examples, to tell the truth. In another thread I mentioned that the documentation of the required order of things in an SQL procedure was hard to find - turns out it is the definition of a compound statement that has it. And the last quote was also in the docs for a compound statement.

None of this is in the SQL Programming book that I can see. I did just look at the v5r1 SQL Programming Concepts book, and that is pretty good, can't see it in an obvious form at 7.1.

Ah well, how often have I felt that the older manuals were more informative?

Rant over - I appreciate especially ideas on the NOOP thing.

Vern