By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

question about dynamic SQL

P: n/a
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!

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
EMPLOYEE_ID is VARCHAR(50).

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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.