Please let me know how I create a c

[DEBUINFO4GL]

Please let me know how I create a cursor in store procedure?

see the below SQL.

SELECT cust_code,cust_name INTO m_code,m_desc
FROM customer WHERE ..............
UNION
SELECT order_no,order_desc INTO m_code,m_desc
FROM order WHERE ...............

The above select are giving error in store procedure, because I am using two time INTO statement.

If I want to avoid INTO statement than I need a cursor in store procedure.

Please can any one help me (How I create a cursor in store procedure).

Thanks
Debu

 

[olded]

Hi:

If tab1 and tab2 are a the same structure, this should work:

DROP PROCEDURE x_proc;
CREATE PROCEDURE x_proc()
RETURNING CHAR(10), CHAR(40);
DEFINE p_1 CHAR(10);
DEFINE p_2 CHAR(40);

FOREACH
SELECT *
INTO p_1, p_2
FROM tab1
UNION
SELECT *
FROM tab2

RETURN p_1, p_2 WITH RESUME;

END FOREACH
END PROCEDURE
# end proc

The trick is to only have the "into" on the first select.

Regards,

Ed