Subject Re: [ib-support] Stored Proc Question
Author Helen Borrie
At 09:03 PM 03-10-01 -0400, you wrote:
> Same old crap I keep forgetting about the : at *** below caused
>Invalid Command error
>on prepare. I will never understand this as examples show things like
> if(:mngr_no IS NULL) THEN
> Ran into this a while back and I think it was Helen who disputed
> DataDef:
>Note Variable names do not need to be—and must not be—preceded by a
>colon in
>stored procedures except in SELECT, INSERT, UPDATE, and DELETE clauses
>where they would
>be interpreted as column names without the colon. Page 150 bottom.

I agree that this is what the documentation says but it doesn't always work this way. Here is a rule that always works for me, as long as I'm careful not to use the same identifiers for both columns and variables:

When the variable on the left side of an assignment or comparison operation, omit the colon; otherwise include it.

So, by rule, the predicate
IF (vrate = :oldpay1) THEN...

should work, and

IF (:vrate = :oldpay1

won't work.

And this is correct:
SELECT.....INTO :vrate

This is not correct:
:vrate = :vrate + 1

This won't work either:

vrate = vrate + 1

It should be
vrate = :vrate + 1

I don't know whether this less-than-helpful piece of documentation resulted from a failed attempt by the programmers to make things work in a more obvious way, or what, but if you follow what the current documentation says, you are going to get errors. The usual one from omitting the colon is "Column VRATE not found" (or something of that ilk).

The question is, should it be treated as a code bug or a documentation bug?