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.

Anyway I know there must be a better way to do this, so I'm open to any
suggestions.

Thanks in advance for any assistance. Here's the SP:

--------
CREATE PROCEDURE QUOTE_REQUEST_CREATE_PRC (
PRQ_DATE DATE,
PRQ_NAME VARCHAR (30),
PRQ_COMPANY VARCHAR (30),
PRQ_PHONE VARCHAR (30),
PRQ_FAX VARCHAR (30),
PRQ_EMAIL VARCHAR (100),
PRQ_COUNTRY VARCHAR (40),
PRQ_WHEN VARCHAR (20),
PRQ_NO_ECLIPSE_CORE INTEGER,
PRQ_QUICKBOOKS VARCHAR (1),
PRQ_PDA VARCHAR (1),
PRQ_NO_WEB INTEGER)
AS
declare variable CONTACT_FOUND integer;
declare variable CONTACT_EMAIL varchar(100);
declare variable CONTACT_ID_FOUND integer;
BEGIN
/* Creates a Price Quote Request & Contact record */
CONTACT_EMAIL = :PRQ_EMAIL;
if(CONTACT_EMAIL <> '') then
begin
CONTACT_FOUND = 0;
SELECT count(*) FROM CONTACT WHERE CN_EMAIL = :CONTACT_EMAIL INTO
:CONTACT_FOUND;
IF(CONTACT_FOUND = 0) then
/* No contact found - create the contact first */
insert into CONTACT
(CN_COMPANY,CN_CONTACT_NAME,CN_EMAIL,CN_PHONE,CN_FAX,CN_COUNTRY)
values (
:PRQ_COMPANY,
:PRQ_NAME,
:PRQ_EMAIL,
:PRQ_PHONE,
:PRQ_FAX,
:PRQ_COUNTRY );
/* Get the Contact ID again
SELECT CN_ID FROM CONTACT WHERE CN_EMAIL = :CONTACT_EMAIL INTO
:CONTACT_ID_FOUND;
if(CONTACT_ID_FOUND <> 0) then
begin
/* Now create the Price Quote request record
insert into PRICE_REQUEST (
PR_DATE,
PR_CN_ID,
PR_NO_ECLIPSE_CORE,
PR_QUICKBOOKS,
PR_PDA,
PR_NO_WEB,
PR_WHEN_BUY )
values (
:PRQ_DATE,
:CONTACT_ID_FOUND,
:PRQ_NO_ECLIPSE_CORE,
:PRQ_QUICKBOOKS,
:PRQ_PDA,
:PRQ_NO_WEB,
:PRQ_WHEN );
end
else
exception CONTACT_NOT_FOUND; */
end
else
exception EMAIL_NOT_SUPPLIED;
SUSPEND;
END

===========================
Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
Web: www.techsol.org