Dear Svein,

I have replace with this :


SET TERM ##;
CREATE PROCEDURE MERGE_CONTENT (SRC INTEGER, DST INTEGER)
RETURNS (Result SMALLINT)
AS
BEGIN
UPDATE REF_MASTER SET CATEGORY_ID = :DST
WHERE
EXISTS(SELECT DISTINCT CONTENT_ID FROM REF_MASTER WHERE
CATEGORY_ID = :SRC
AND NOT EXISTS( SELECT CONTENT_ID FROM REF_MASTER WHERE
CATEGORY_ID = :DST))
AND CATEGORY_ID = :SRC;
DELETE FROM M_CATEGORIES WHERE CATEGORY_ID = :SRC;
Result = 1;
EXIT;
END ##

but when i "EXECUTE PROCEDURE MERGE_CONTENT(1,1)" it not return value of
Result , why ??

-----Original Message-----
From: Svein Erling Tysvær
[mailto:svein.erling.tysvaer@...]
Sent: Friday, July 12, 2002 9:15 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] return value


Diko,
there are two kinds of stored procedures - executable and those you select
from. SUSPEND should only be used with selectable SPs, which your SP does
not appear to be.

Another thing is that using IN (<subselect>) is terribly slow and that your
procedure is very likely to get serious performance problems once there
gets a bit of data into your tables. Get used to using EXISTS, which is
much faster. In your case that would be something like

UPDATE REF_MASTER A SET CATEGORY_ID = :DST
WHERE EXISTS(SELECT 1 FROM REF_MASTER B
WHERE A.CONTENT_ID = B.CONTENT_ID
AND B.CATEGORY_ID = :SRC
AND NOT EXISTS (SELECT 1 FROM REF_MASTER C
WHERE B.CONTENT_ID=C.CONTENT_ID
AND C.CATEGORY_ID = :DST))
AND CATEGORY_ID = :SRC;

HTH,
Set


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/