Rotandiko Sastroprawiro wrote:

>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 ??
>

Because you should place the return value in some variable. Try this:

declare variable rc smallint;
...
EXECUTE PROCEDURE MERGE_CONTENT 1,1 RETURNING_VALUES :rc;

>
>-----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/
>
>
>
>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/
>
>
>
>
>