IF ( ACTIVITY_COUNT < :pvRowsNeeded ) THEN SIGNAL SQLSTATE 'U0001' SET MESSAGE_TEXT='SP_ONBOARD_JOURNEY_TTS - Failed to INSERT properly, not enough rows selected'; END IF;
pvRowsNeeded is an integer parameter passed to the stored proc.
When I replace the parm with "5000", the procedure will compile.
Solved! Go to Solution.
Simply remove the colon in front of the parameter, it's an SP, not a macro :-)
IF ( ACTIVITY_COUNT < pvRowsNeeded ) THEN SIGNAL SQLSTATE 'U0001' SET MESSAGE_TEXT='SP_ONBOARD_JOURNEY_TTS - Failed to INSERT properly, not enough rows selected'; END IF;
REPLACE PROCEDURE NDW_EBI_WORK.SP_ONBOARD_JOURNEY_CALLS ( IN pvRowsNeeded INTEGER, IN pvDaysBack INTEGER ) BEGIN DECLARE vCNT SMALLINT; DECLARE vSQL_TEXT VARCHAR(10000); DELETE FROM ndw_ebi_work.move_onbrd_conn_calls_wz WHERE activity_date > ( SELECT Max(activity_date) - :pvDaysBack - 1 FROM ndw_ebi_work.move_onbrd_conn_calls_wz);
The colon prefix is allowed or even required within DML statements (e.g. DELETE), but not in SQL Control Statements.
Thanks Fred....but my stored proc compiled fine WITHOUT the colon.
Make sure your compiler's parsing routine vets this condition properly and throws an error if a colon is required... or not.
Not sure I understand your last comment.
For your DELETE statement example:
For the original IF statement, a colon prefix is not allowed.