Re: How to modify a stored procedure

David FOXWELL wrote:

Charles Wilt wrote:

But it'd seem to me that while STRSQL doesn't want the final
semicolon ; it would still need any in the middle...

CREATE PROCEDURE FOXWELL/DOAATN001 ( IN CLIENT_NO NUMERIC(9, 0) )
CASE
WHEN CLIENT_NO <> 0
THEN
UPDATE DOAATN SET STRTFL = 'N'
, SMAJHT = CURRENT TIME
WHERE SCLTNO = CLIENT_NO;
ELSE
UPDATE DOAATN SET STRTFL = 'N'
, SMAJHT = CURRENT TIME;
END CASE

Now it's saying that CASE is misplaced. If I take off the CASE and
just have the UPDATE, then UPDATE is misplaced. Ok, Friday
afternoon's not the best time to try. Try again Monday morning!

Besides having removed more than the last semicolon in the first attempt, there appears to have been excessive trimming of that source for the later attempts. Most important, at a minimum, the LANGUAGE SQL is required before the CASE. Try the following:

<code>

CREATE PROCEDURE FOXWELL/MYPROC
( IN CLIENT_NO NUMERIC(9, 0) )
LANGUAGE SQL
SPECIFIC FOXWELL/MYPROC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD
, ALWCPYDTA = *OPTIMIZE
, COMMIT = *NONE
, DECRESULT = (31, 31, 00)
, DFTRDBCOL = *NONE
, DYNDFTCOL = *NO
, DYNUSRPRF = *USER
, SRTSEQ = *HEX
CASE
WHEN CLIENT_NO <> 0
THEN UPDATE MYFILE SET STRTFL = 'N'
, SMAJHT= CURRENT TIME
WHERE SCLTNO = CLIENT_NO
;
ELSE UPDATE MYFILE SET STRTFL = 'N'
, SMAJHT= CURRENT TIME
;
END CASE

</code>

In the RUNSQLSTM source and Run SQL from iNav, a semicolon can appear following the statement _even though_ it is syntactically incorrect, *only* because they are both script processors. For those interfaces, the semicolon suggests the data following the final semicolon is another statement; but the EOF indicates the last statement is effectively an empty string, it is just ignored. The STRSQL interpreter supports only one statement at a time, so the superfluous semicolon is flagged as an error.

Regards, Chuck