question about dynamic SQL

[lizayica@yahoo.ca]

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(sa lary) as amt '
||'FROM HR.EMPLEE_SALAR Y'
||' 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]

Re: question about dynamic SQL

lizayica@yahoo. ca wrote:[color=blue]
> 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);[/color]
DECLARE V_CUR STATEMENT; -- not really needed but good style.[color=blue]
> DECLARE C_AMT CURSOR FOR V_CUR[/color]
WITH RETURN TO CLIENT;[color=blue]
>
> SET V_DYN_SQL = 'SELECT sum(coalesce(sa lary) as amt '
> ||'FROM HR.EMPLEE_SALAR Y'
> ||' WHERE EMPLOYEE_ID IN ('|| EMPLOYEE_ID ||')';
>
> PREPARE V_CUR FROM V_DYN_SQL;
>
> OPEN C_AMT;
> END P1[/color]

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

Re: question about dynamic SQL

EMPLOYEE_ID is VARCHAR(50).

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