On 14 Apr 2013 16:05, Vernon Hamberg wrote:
<<SNIP>>
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.
<<SNIP>>
IMO a requirement for an ELSE enforces a good programming habit; i.e.
any failed assumptions should always be handled or notified. If the
assumption is that every known condition would be handled by the WHEN
clauses coded prior, then the ELSE should be coded with something like
the following statement, to inform that the assumption was incorrect;
such is often referred to in computing as an "assertion":
SIGNAL sqlstate value 'UC001' set message_text='Unexpected case';
Instead of SIGNAL, a CALL could be made to a routine that dumps
information about the failed assumption; probably passed as a literal
string specified in the source code; e.g.:
CALL ASSUME ('stCount value should only =1 or =2');
However if choosing to use SET, then rather than set any variable to
itself, I would declare a variable, perhaps named NOOP, and set that
variable to any compatible value whenever\wherever I wanted the
null\empty statement. This might be what was described as "just
anything", but I could make no sense of what was meant. e.g.:
case stCount
when 1 then set wc = 'One' ;
when 0 then set wc = 'Two' ;
else set NOOP = 0 ; /* trivial work; token NOOP appears */
end case ;
Otherwise there are GOTO, LEAVE, and possibly ITERATE or RETURN
control statements, depending on the context.
To use a GOTO to get after the CASE, requires that some other
statement follows the label; e.g.:
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else goto stCaseEnd ; /* effectively a NOOP */
end case ;
stCaseEnd:
/* some statement is required here; i.e. can not be an "end" */
If necessary, the CASE may be set within its own compound statement
to accomplish a LEAVE to just after the CASE, if leaving the routine is
not desirable by LEAVE procname or RETURN integer; e.g.:
stCase:
begin
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else LEAVE stCase ; /* effectively a NOOP */
end case ;
end stCase ; /* LEAVE jumps to here */
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 <<SNIP>>
The SQL procedure statement noted in the syntax diagram for the
handler can be a compound statement [as later comments acknowledge;
though they are snipped]. In my experience the compound statement can
be /empty/ such that there is just a BEGIN and END.
Thinking on that... I tried the same for the ELSE, and the same form
of an /empty/ request functioned for the ELSE as well; just as it does
for a condition handler. Maybe not as nice as a NOOP or a just a semicolon:
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else begin end ; /* empty request */
end case ;
midrange.com
