Hi,

> SET TERM ##;
> CREATE PROCEDURE GET_OBJECT_TYPE_ID1 (ObjectTypeId INTEGER)
> RETURNS (Result INTEGER)
> AS
> DECLARE VARIABLE TEMP1 INTEGER;
> DECLARE VARIABLE TEMP2 INTEGER;
> BEGIN
> BEGIN
> FOR SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
> WHERE OBJECT_TYPE_ID = :ObjectTypeId
> INTO :TEMP1

<snip>

> END ##
>
> The result if i am running it : "SELECT * FROM GET_OBJECT_TYPE_ID(4)"
> is 4,10,11,12,13,48,48,49,50,51,52,4,10,11,12,13,4,10,11,12,13


> but i want 4,10,11,12,13,48,49,50,51,52.
>
> please help, what's wrong with this procedure ?

Avoid the IN because the SELECT .... is called for every row.
When you use IN with constants there's no performance decrease.
In a stored procedure you can use FOR .. instead.


If the order isn't importent for you then look at the example below.
It's a simpler procedure but i think giving the result you want.
The procedure is calling itselfs for every parent_object_id.


CREATE PROCEDURE GET_OBJECT_TYPE_ID (OBJECTTYPEID Integer) returns (RESULT
Integer) AS
DECLARE VARIABLE TEMP INTEGER;
BEGIN
RESULT = :OBJECTTYPEID;
SUSPEND;
FOR
SELECT
OBJECT_TYPE_ID
FROM
M_OBJECT_TYPE
WHERE
PARENT_OBJECT_TYPE_ID = :OBJECTTYPEID
INTO
:TEMP
DO
BEGIN
FOR
SELECT
RESULT
FROM
GET_OBJECT_TYPE_ID(:TEMP)
INTO
:RESULT
DO
SUSPEND;
END
END



Regards,
Arno Brinkman
ABVisie