Re: NOOP in SQL procedure? And other questions

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 ;