Thanks Helen and Lucas :)

I think the ROW_COUNT variable is what I was looking for.
However, I have
another question to ask now:

Till now I have designed the SPs in such a way that a SELECT
COUNT(*) FROM
<table> WHERE <condition> is executed on the table in question.
If the
return value is zero, I would attempt an INSERT, and if the
return value was
1, I would attempt an UPDATE. My senior told me the COUNT(*)
function will
slow down the procedure in case the table has a huge number of
records, and
that I should get rid of it. By that logic, would the SELECT 1
FROM ...
statement also slow down the procedure?

In short, from the two SELECT queries below, which one would
work faster?

1. SELECT COUNT(*) FROM <table> WHERE <condition>

2. SELECT 1 FROM <table> WHERE <condition>



And then, if I used the ROW_COUNT variable, which of the
following would
work faster?

1. UPDATE <table> SET .... WHERE <condition>
IF ROW_COUNT = 0 THEN
INSERT INTO <table> VALUES <values>


2. IF (EXISTS (SELECT 1 FROM <table> WHERE <condition>)) THEN
UPDATE <table> SET .... WHERE <condition>
ELSE
INSERT INTO <table> VALUES <values>



Thanks and Regards,

Anand S. Kashelkar
Mumbai

----- Original Message -----

Message: 16
Date: Thu, 18 Dec 2003 00:03:02 +1100
From: Helen Borrie <helebor@...>
Subject: Re: Getting number of records affected ...

At 04:34 AM 17/12/2003 -0800, you wrote:
>I am trying to write a stored procedure that will attempt an
>Update ...
>statement first. Then it should check for the number of
records
>affected by
>the Update statement, and if the number of records affected are
>zero, it
>should issue an Insert... statement.
>
>How do I find out the number of records affected by an action
>query?

In Firebird 1.5 you can read the ROW_COUNT variable after an
update. But
your strategy here is to do an existence check, which avoids the
update
operation altogether if there are no rows to update:

if (exists (select 1 from atable where akey = :akey and ....))
then
update atable set.....;
else
insert into atable(..........

heLen

________________________________________________________________________
________________________________________________________________________

Message: 17
Date: Wed, 17 Dec 2003 13:45:56 +0100
From: Lucas Franzen <luc@...>
Subject: Re: Getting number of records affected ...

Anand,


Anand schrieb:

> I am trying to write a stored procedure that will attempt an
> Update ...
> statement first. Then it should check for the number of
records
> affected by
> the Update statement, and if the number of records affected
are
> zero, it
> should issue an Insert... statement.

> How do I find out the number of records affected by an action
> query?

with FB1.5 you can use ROW_COUNT,

Example:

UPDATE MYTABLE SET MYFIELD = 0 WHERE MYCONSITION = 'Blabla'

IF ( ROW_COUNT ) = 0 THEN
BEGIN
INSERT INTO MYTABLE ...
END



If you use FB prior to 1.5 you can do sth. like:


...
declare variable IS_RECORD_THERE integer;
...

SELECT 0 FROM RDB$DATABASE
WHERE NOT EXISTS ( <update_condition> )
INTO :IS_RECORD_THERE;

IF ( IS_RECORD_THERE = 0 ) THEN
BEGIN
/* do your insert */
END



Luc.

________________________________________________________________________
________________________________________________________________________



__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/