From: "Andrew" <andrew_s_vaz@...>
Sent: Tuesday, January 31, 2012 3:04 PM
To: <firebird-support@yahoogroups.com>
Subject: [firebird-support] Stored Procedure question.

> Hi all,
>
> I'm trying to do a stored procedure that does a select with parameters and
> then, if records aren't found, do the insert and proceed with the DML.
>
> I have to implement this as a Stored procedure (thrid party app) and I'm
> stumped. I tried to google it but there were a gazzilion examples and none
> of them had this example.
>
> something like:
>
> Select name, surname, address from client where SSN = 1234
> if not found (or recordcount = 0, or empty = true) then
> insert (name, surname, address, SSN) in client ('john', 'doe', 'aa
> street', 1234);
>
> Edit client set total_bought = total_bought + 100.00;

Try something like:

if not exists(select 1 from client where SSN = :SSN) then
begin
insert into client (name, surname, address, SSN, total_bought)
values (:Name, :SurName, :Address, :SSN, :Amount);
end else
begin
update client set total_bought = total_bought + :Amount
where SSN = :SSN;
end;


Each of the variables with the colon ( : ) in front of them should be passed
in as arguments. This is just one way to accomplish what you want. I'm sure
you'll get several examples.

HTH
Woody (TMW)