--- In firebird-support@yahoogroups.com, "Woody" <woody-tmw@...> wrote:
>
> 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)
>