question about dynamic SQL

[lizayica]

I nees create a procedure that return a cursor for Crystal report. I
need use dynamic SQL.
what's wrong with the following procedure ? (that only part of the
sample)

CREATE PROCEDURE CARD.PROCEDURE1 (IN EMPLOYEE_ID VARCHAR(50) )
LANGUAGE SQL
SPECIFIC SP_TEST_DYN
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE V_DYN_SQL VARCHAR(200);

DECLARE C_AMT CURSOR FOR V_CUR;

SET V_DYN_SQL = 'SELECT sum(coalesce(salary) as amt '
||'FROM HR.EMPLEE_SALARY'
||' WHERE EMPLOYEE_ID IN ('|| EMPLOYEE_ID ||')';

PREPARE V_CUR FROM V_DYN_SQL;

OPEN C_AMT;
END P1
Thanks a lot for any help!

[Serge Rielau]

li******@yahoo.ca wrote:

I nees create a procedure that return a cursor for Crystal report. I
need use dynamic SQL.
what's wrong with the following procedure ? (that only part of the
sample)

CREATE PROCEDURE CARD.PROCEDURE1 (IN EMPLOYEE_ID VARCHAR(50) )
LANGUAGE SQL
SPECIFIC SP_TEST_DYN
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE V_DYN_SQL VARCHAR(200); DECLARE V_CUR STATEMENT; -- not really needed but good style. DECLARE C_AMT CURSOR FOR V_CUR WITH RETURN TO CLIENT;
SET V_DYN_SQL = 'SELECT sum(coalesce(salary) as amt '
||'FROM HR.EMPLEE_SALARY'
||' WHERE EMPLOYEE_ID IN ('|| EMPLOYEE_ID ||')';

PREPARE V_CUR FROM V_DYN_SQL;

OPEN C_AMT;
END P1

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

EMPLOYEE_ID is VARCHAR(50).

||' WHERE EMPLOYEE_ID IN ('''|| EMPLOYEE_ID ||''')';