Jack,


jrodenhi schrieb:

> Hi,
> I'm just sort of starting to reach a little farther with Firebird. Up
> to now, I have pretty much limited my activities to simple
> Insert, Update and Select activities in my database. Now I'm starting
> to check out the selectable stored procedures. This is a pretty slick
> feature. I just finished a little procedure that I use with
> Fast-Reports to print checks. If you select from the procedure while
> giving it a zero parameter, it returns the next check number from the
> Bank record. If you give it a non-zero parameter, it returns that
> same number to you as the next check number and updates the Bank to
> start the numbering there. It also updates the payable with the check
> number. That's nice.

Usually you shouldn't do DML in selectable procedures.

>
> My question is this. I really like this power but I spent a fair
> amount of time debugging the procedure. I learned one little trick
> while I was developing it but I'll bet there are some things that some
> of the more experienced developers are doing that won't be apparent to
> me for a long time, if ever. Have you come up with any tricks for
> debugging stored procedures that help you a lot? Do you have any UDFs
> that you use to help in debugging? Do you do anything
> different with triggers? Or, maybe a little simpler, do you have a
> simple rule for when you use a colon with a variable and when you don't?

Once you get acquainted with the syntax it's quite easy, thus debugging
will be necessary only rarely.

Some DB-tools (DBWorkbench, IBExpert) offer a SP debugger but you
shouldn't trust them always 100%.

If you're stuck try to add some error handling (ie WHEN ANY...) and
return kind of a loop variable to see at which stage the sp's not
working correctly (given that the sp is compiled and you're not having
troubles with the syntax).
In extreme cases you might think about adding a table to your database
where a sp might write its current state and values into.


Use the colon whenever it's part of an SQL statement or when used to
select into, never in the other cases.

declare variable MYVAR VARCHAR(20);

/* ----------------------------------------- */
/* you have to use it in the following cases */
/* ----------------------------------------- */
FOR SELECT MYFIELD FROM MYTABLE
INTO :MYVAR


SELECT SOMEFIELDS FROM MYTABLE
WHERE MYFIELD = :MYVAR



EXECUTE PROCEDURE SP_SOMETHNG
RETURNING VALUES :MYVAR


INSERT INTO MYTABLE ( MYFIELD )
VALUES ( :MYVAR );



UPDATE MYTABLE SET
MYFIELD = :MYVAR;



/* ------------------------------------- */
/* don't use it when accessing the value */
/* ------------------------------------- */

IF ( MYVAR = '4711' ) THEN


MYVAR = MYVAR || '1';




HTH

Luc.