> Forgive my ignorance at doing heavy duty Firebird Stored
> Procedures, but I'm
> trying to find out what I'm doing wrong with this one, and curb the
> behavior. All help is greatly appreciated.
>
> What I'm trying to do here is to have a stored procedure that will accept
> incoming values, and as a result create a row in two tables, one for a
> quotation request, and the other for a record of the contact who requested
> the quote. If the contact already exists, I don't need to create the
> contact record but just to link the quote to that existing record. Pretty
> simple enough.
>
> The problem is that when I try and run this, it accepts it fine in the
> database but never seems to work when I run it. If I try and add a record
> with this, where I know the email address supplied is not on file, I never
> seem to get back a correct value in CONTACT_FOUND (the count of
> the records
> found). I would expect it to return a 0 records found, which it does if I
> manually process the SQL statement for it, but in the stored procedure it
> never seems to be 0 in the if then statement.
>

try using UPPER() on both in the comparison
why do you use another variable and not just the passed in parameter where
you need it? I have a feeling that you can only use the param once so in the
contact insert, use the CONTACT_EMAIL variable, not the passed in parameter.
Do you have a unique constraint on the email column? You're assuming unique
status here.
and instead of inserting a record and then getting back the ID with a
select, get the gen value first into a variable, then insert the main
record, and then it's keyed records using the value. You will not suffer
from multiple (duplicate records) in the select and it's just a better way
to do it when you have to do this kind of thing.
Alan